Could someone tellme what this code does? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Could someone tellme what this code does?

Author

Topic

Riku Tuominen
Starting Member

22 Posts

Posted - 2002-06-24 : 07:25:03

Could some one tell me in simple english what this code actualy does.
Don't understand the this with reult 1 or 0?

SELECT
MONTH(OrderDate) AS OrderMonth,
SUM(CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1996' THEN 1
ELSE 0
END) AS c1996,
SUM(CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1997' THEN 1
ELSE 0
END) AS c1997,
SUM(CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1998' THEN 1
ELSE 0
END) AS c1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)

MakeYourDaddyProud

184 Posts

Posted - 2002-06-24 : 07:50:27

Lets break it into parts...

Essentially everthing will be returned in month order with a maximum list of 12 lines are the portion function MONTH just returns the month part of the OrderDate...

This just lists the months in order

SELECT MONTH(OrderDate) AS OrderMonth
FROM T_Supplier_Agent_Cust_Balances
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)

... but just list the month is not enough information, the coder wanted some information about specific years for each of the twelve months so the CASE statement...

(CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1998' THEN 1
ELSE 0
END)

... is designed to return a boolean 1 for the year in question (year portion of the OrderDate) and 0 otherwise. This operates at each row of the dataset for each month. Since an aggregated function is required over the data by virtue of the GROUP BY, the SUM in each ...

SUM (CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1996' THEN 1
ELSE 0
END)

SUM (CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1997' THEN 1
ELSE 0
END)

SUM (CASE CAST(YEAR(OrderDate) AS nvarchar(100))
WHEN N'1998' THEN 1
ELSE 0
END)

... will increment by once every occurrence of a row in the data where the year matches i.e.

(WHEN N'199?' THEN 1 ELSE 0)

So the full query return a consolidated 12 month list of sales made in each respective month in the cross tables years reported.

HTH

Danno
www.danielsmall.com IT Factoring

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-24 : 07:52:42

Well, it can be simplified a bit:

SELECT
MONTH(OrderDate) AS OrderMonth,
SUM(CASE YEAR(OrderDate) WHEN 1996 THEN 1
ELSE 0
END) AS c1996,
SUM(CASE YEAR(OrderDate) WHEN 1997 THEN 1
ELSE 0
END) AS c1997,
SUM(CASE YEAR(OrderDate) WHEN 1998 THEN 1
ELSE 0
END) AS c1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)

You don't need to CAST the year values to nvarchar in order to test their values.

Each CASE statement evaluates whether a date value occurs in a particular year (1996, 97, or 98) and then returns a 1 if it matches that year or a zero otherwise. The SUM function sums them up, and the GROUP BY creates groups for each month (1-12). Since only 1 and 0 are being summed, this is equivalent to counting how many orders there were for each month in each of the three years.

The reason to use SUM and not COUNT is that COUNT will ignore null dates or missing orders for a month. For example, if there were no orders for October 1996, COUNT would not return anything, not even a zero. Using SUM would allow you to get a zero for that month and year.

Just like Danny said...

Snipers are everywhere! Run! Run!

Edited by - robvolk on 06/24/2002 07:54:13

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-06-24 : 07:57:28

This query will return you the count for each month that has data in order table for year 96,97 and 98.

e.g.
--------------------------------------
ordermonth c1996 c1997 c1998
--------------------------------------
jan 1 5 0
feb 0 0 2
...
...
...
dec 2 2 1
--------------------------------------

MakeYourDaddyProud

184 Posts

Posted - 2002-06-24 : 08:01:18

Yeah Rob, I agree with that CAST - I was wondering what the coder expected with that??? Your thoughts...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-24 : 08:53:11

Probably an example from a book or courseware, copied verbatim. It doesn't hurt really, it's just not necessary for the query to work.

Riku Tuominen
Starting Member

22 Posts

Posted - 2002-06-24 : 15:28:32

Wow that was fast response.
Thanks I understod it now.
The code was part of a run stored procedure.
Cast was used cause a wizard created the select string and
wizard supported also non integer fields.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources