SQL Syntax Question

[ghost2]

I have a query where I am summing sales for the current year 2004. This query is grouping by year and month based in criteria for the current year. I need to also though in a column summarize 2003 all data not by month, but grouped the same way on the channel. Here is the query any help would be appreciated. I also will show the nested select I tried but I got the same number all the way down the column. Thanks all.

ORIGINAL QUERY WITHOUT THE SUM FOR 2003. The Case statement in the WHERE Clause just gets the dateshipped between 1/1/2004 - todays date time.

SELECT
Year([DateShipped]) AS DYear, Month([DateShipped]) AS DMonth,DATENAME(month, [DateShipped]) AS 'Month Name'
,CASE WHEN tblSALESDIVISIONS.DIVISION IS NULL THEN 'Zone' ELSE tblSALESDIVISIONS.DIVISION END AS DIVISION,
ISNULL( tblDistChannel.CHANNEL, 'Unassigned') AS CHANNEL,
Case WHEN tblDistChannel.CUSTOMER IS NULL then RM00101.STMTNAME else
tblDistChannel.CUSTOMER END AS CUSTOMER,
sum(Sales_Orders_Details.OrderQuantity * ( Sales_Orders_Details.UnitPrice - Sales_Orders_Details.UnitDiscount )) as Gross
FROM ((Sales_Orders_Master_File INNER JOIN Sales_Orders_Details ON Sales_Orders_Master_File.SalesOrderNo = Sales_Orders_Details.SALESORDERNO) LEFT JOIN (tblDistChannel LEFT JOIN tblSALESDIVISIONS ON tblDistChannel.CHANNEL = tblSALESDIVISIONS.CHANNEL) ON Sales_Orders_Master_File.CustomerNo = tblDistChannel.CUSTNMBR) INNER JOIN RM00101 ON Sales_Orders_Master_File.CustomerNo = RM00101.CUSTNMBR
WHERE CUSTCLAS <> 'DTC' and [OrderStreamType]<>2 and Sales_Orders_Master_File.OrderStatusCode <> 15 AND Sales_Orders_Details.Itemtype <> 0
AND (DateShipped between cast (cast(datepart(year,getdate()) as char(4)) + '-' + '01-01'
as datetime) and cast (cast(datepart(year,getdate()) as char(4)) + '-' +
substring(convert(char(2),getdate(),101),1,2) + '-' + cast(datepart(day,getdate()) as char(2))
as datetime))

GROUP BY Year([DateShipped]) , Month([DateShipped]),DATENAME(month, [DateShipped]), CASE WHEN tblSALESDIVISIONS.DIVISION IS NULL THEN 'Zone' ELSE tblSALESDIVISIONS.DIVISION END,ISNULL( tblDistChannel.CHANNEL, 'Unassigned') ,
Case WHEN tblDistChannel.CUSTOMER IS NULL then RM00101.STMTNAME else
tblDistChannel.CUSTOMER END

QUERY I TRIED WITH NESTED SELECT BUT GOT SAME NUMBER ALL THE WAY THROUGH.

SELECT
Year([DateShipped]) AS DYear, Month([DateShipped]) AS DMonth,DATENAME(month, [DateShipped]) AS 'Month Name'
,CASE WHEN tblSALESDIVISIONS.DIVISION IS NULL THEN 'Zone' ELSE tblSALESDIVISIONS.DIVISION END AS DIVISION,
ISNULL( tblDistChannel.CHANNEL, 'Unassigned') AS CHANNEL,
Case WHEN tblDistChannel.CUSTOMER IS NULL then RM00101.STMTNAME else
tblDistChannel.CUSTOMER END AS CUSTOMER,
(SELECT sum(Sales_Orders_Details.OrderQuantity * ( Sales_Orders_Details.UnitPrice - Sales_Orders_Details.UnitDiscount ))
FROM ((Sales_Orders_Master_File INNER JOIN Sales_Orders_Details ON Sales_Orders_Master_File.SalesOrderNo = Sales_Orders_Details.SALESORDERNO) LEFT JOIN (tblDistChannel LEFT JOIN tblSALESDIVISIONS ON tblDistChannel.CHANNEL = tblSALESDIVISIONS.CHANNEL) ON Sales_Orders_Master_File.CustomerNo = tblDistChannel.CUSTNMBR) INNER JOIN RM00101 ON Sales_Orders_Master_File.CustomerNo = RM00101.CUSTNMBR
WHERE CUSTCLAS <> 'DTC' and [OrderStreamType]<>2 and Sales_Orders_Master_File.OrderStatusCode <> 15 AND Sales_Orders_Details.Itemtype <> 0
AND (DateShipped between cast (cast(datepart(year,getdate())-1 as char(4)) + '-' + '01-01'
as datetime) and cast (cast(datepart(year,getdate())-1 as char(4)) + '-12-31'
as datetime))
AND tblDistChannel.Channel = tblDistChannel.Channel
) AS Gross03,
sum(Sales_Orders_Details.OrderQuantity * ( Sales_Orders_Details.UnitPrice - Sales_Orders_Details.UnitDiscount )) as Gross
FROM ((Sales_Orders_Master_File INNER JOIN Sales_Orders_Details ON Sales_Orders_Master_File.SalesOrderNo = Sales_Orders_Details.SALESORDERNO) LEFT JOIN (tblDistChannel LEFT JOIN tblSALESDIVISIONS ON tblDistChannel.CHANNEL = tblSALESDIVISIONS.CHANNEL) ON Sales_Orders_Master_File.CustomerNo = tblDistChannel.CUSTNMBR) INNER JOIN RM00101 ON Sales_Orders_Master_File.CustomerNo = RM00101.CUSTNMBR
WHERE CUSTCLAS <> 'DTC' and [OrderStreamType]<>2 and Sales_Orders_Master_File.OrderStatusCode <> 15 AND Sales_Orders_Details.Itemtype <> 0
AND (DateShipped between cast (cast(datepart(year,getdate()) as char(4)) + '-' + '01-01'
as datetime) and cast (cast(datepart(year,getdate()) as char(4)) + '-' +
substring(convert(char(2),getdate(),101),1,2) + '-' + cast(datepart(day,getdate()) as char(2))
as datetime))

GROUP BY Year([DateShipped]) , Month([DateShipped]),DATENAME(month, [DateShipped]), CASE WHEN tblSALESDIVISIONS.DIVISION IS NULL THEN 'Zone' ELSE tblSALESDIVISIONS.DIVISION END,ISNULL( tblDistChannel.CHANNEL, 'Unassigned') ,
Case WHEN tblDistChannel.CUSTOMER IS NULL then RM00101.STMTNAME else
tblDistChannel.CUSTOMER END

 

[donutman]

Make your question clear. What does <I need to also though in a column summarize 2003 all data not by month, but grouped the same way on the channel. > mean? Channel? I've never heard that word used about SQL before.

Do you mean that you would like 2003 and 2004 data to be returned within the same row? If that is what you want then just create a 2003 view and a 2004 view and inner join them on the month.
-Karl

 

[ghost2]

Yes Channel is just a field in the query. My result set without the 2003 data using only the date between 1/1/2004 to 4/7/2004 is like so:

Year, Month, MonthName, Division, Channel, Cust, Gross
2004 1 January Capital Drug 12 150.00
2004 2 February House Food 27 75.25

Now what I am trying to get is another column with the 2003 TOTAL SUM for 2003 for that whole year 1/1/2003-12/31/2003 for the channel. So I know this would give me the same summed number for every record that had the Channel "Drug" in it etc. So for example:

Here an example result set again with what I want:
Year, Month, MN, Division, Channel, Total03 Cust, Gross
2004 1 Jan Capital Drug 2540.05 12 150.00
2004 2 Feb House Food 3452.10 27 75.25
2004 3 Mar Capital Drug 2540.05 16 125.25

 

[donutman]

No problem. Just do what I suggested. Write a view that creates the annual totals and make that view include a field for the month and one for the year. Now do an inner join on the 2004 data and this new view. Join on year=year-1 and month=month. Is that clear or should I spell it out?
-Karl

 

[SQLSister]

I agree with donutman (except I wouldn't make it a view just select queries used as derived tables), but use a full join instead of an inner join, you want the records which might have a 2003 total but none so far in 2004 and the records which had no 2003 total but do have 2004 totals I presume.

Somting like (much simplified and you need to substitute the real calculations):

Select isnull(a.channel, b.channel), Sum(mon1), Sum (mon2), sum(Total2003) from
(select channel, date, sum(mon1)as mon1, sum(mon2)as mon2 from table1 group by channel,date having date>='1/1/2004')a 
full outer join
(Select channel, date sum (mon1) + sum (mon2) as Total2003 from table1 group by channel,date having date<'1/1/2004' and date>='1/1/2003') b
on a.channel = b.channel
group by channel

 

[ghost2]

Thanks alot guys..