December 3, 2009 at 3:13 pm
Howdy all...
I'm having a problem with the following query:
declare @Year varchar(30)
set @Year = '2009'
SELECT PART.DESCRIPTION,
sum(case when invoice_date between '1/1/'+@Year and '1/31/'+@Year then total_amount else 0 end) as 'Jan',
sum(case when invoice_date between '2/1/'+@Year and '2/28/'+@Year then total_amount else 0 end) as 'Feb',
sum(case when invoice_date between '3/1/'+@Year and '3/31/'+@Year then total_amount else 0 end) as 'Mar',
sum(case when invoice_date between '4/1/'+@Year and '4/30/'+@Year then total_amount else 0 end) as 'Apr',
sum(case when invoice_date between '5/1/'+@Year and '5/31/'+@Year then total_amount else 0 end) as 'May',
sum(case when invoice_date between '6/1/'+@Year and '6/30/'+@Year then total_amount else 0 end) as 'Jun',
sum(case when invoice_date between '7/1/'+@Year and '7/31/'+@Year then total_amount else 0 end) as 'Jul',
sum(case when invoice_date between '8/1/'+@Year and '8/31/'+@Year then total_amount else 0 end) as 'Aug',
sum(case when invoice_date between '9/1/'+@Year and '9/30/'+@Year then total_amount else 0 end) as 'Sep',
sum(case when invoice_date between '10/1/'+@Year and '10/31/'+@Year then total_amount else 0 end) as 'Oct',
sum(case when invoice_date between '11/1/'+@Year and '11/30/'+@Year then total_amount else 0 end) as 'Nov',
sum(case when invoice_date between '12/1/'+@Year and '12/31/'+@Year then total_amount else 0 end) as 'Dec',
sum(case when year(invoice_date) = @Year then total_amount else 0 end) as 'Year Total'
FROM PART AS PART INNER JOIN
CUST_ORDER_LINE AS CUST_ORDER_LINE INNER JOIN
RECEIVABLE_LINE AS RECEIVABLE_LINE INNER JOIN
RECEIVABLE AS RECEIVABLE ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID ON
CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO ON
PART.ID = CUST_ORDER_LINE.PART_ID
WHERE (PART.PRODUCT_CODE = 'TRAILER') AND (PART.ID NOT LIKE 'UPG%') AND (RECEIVABLE_LINE.REFERENCE LIKE '[1-9]%')
AND (RECEIVABLE.CUSTOMER_ID='i69tra')AND PART.USER_1 = 'Dump' AND 'Year Total' != '0.00'
group by PART.DESCRIPTION
The results :
6 X 12 Work Series Dump 10K0.000.000.000.000.000.000.000.000.000.000.000.000.00
6 x 8 Work Series Dump 5K0.000.000.000.000.000.000.000.000.000.000.000.000.00
62 IN X 8 LProfile Dump 5K Single Ram0.002525.002810.005160.000.000.000.000.000.000.000.000.0010495.00
80 IN X 12 LProfile 12K Dual Ram Dump0.000.008200.004470.0035000.0012900.000.000.000.000.003870.000.0064440.00
80 IN X 12 LProfile Dump Sprg #70000.000.000.000.000.000.000.000.000.000.000.000.000.00
80 IN X 12 LProfile Scissor Dump 12K0.000.004900.000.000.000.000.000.000.000.000.000.004900.00
80 IN X 12 LProfile Scissor Dump 14K0.000.000.000.000.000.000.000.000.000.000.000.000.00
How do I get rid of the Rows that contain a yearly total of 0.0?
Thank you guys..
December 3, 2009 at 3:24 pm
Personally, I'd rewrite this but here is what you should try:
SELECT PART.DESCRIPTION,
sum(case when invoice_date between '1/1/'+@Year and '1/31/'+@Year then total_amount else 0 end) as 'Jan',
sum(case when invoice_date between '2/1/'+@Year and '2/28/'+@Year then total_amount else 0 end) as 'Feb',
sum(case when invoice_date between '3/1/'+@Year and '3/31/'+@Year then total_amount else 0 end) as 'Mar',
sum(case when invoice_date between '4/1/'+@Year and '4/30/'+@Year then total_amount else 0 end) as 'Apr',
sum(case when invoice_date between '5/1/'+@Year and '5/31/'+@Year then total_amount else 0 end) as 'May',
sum(case when invoice_date between '6/1/'+@Year and '6/30/'+@Year then total_amount else 0 end) as 'Jun',
sum(case when invoice_date between '7/1/'+@Year and '7/31/'+@Year then total_amount else 0 end) as 'Jul',
sum(case when invoice_date between '8/1/'+@Year and '8/31/'+@Year then total_amount else 0 end) as 'Aug',
sum(case when invoice_date between '9/1/'+@Year and '9/30/'+@Year then total_amount else 0 end) as 'Sep',
sum(case when invoice_date between '10/1/'+@Year and '10/31/'+@Year then total_amount else 0 end) as 'Oct',
sum(case when invoice_date between '11/1/'+@Year and '11/30/'+@Year then total_amount else 0 end) as 'Nov',
sum(case when invoice_date between '12/1/'+@Year and '12/31/'+@Year then total_amount else 0 end) as 'Dec',
sum(case when year(invoice_date) = @Year then total_amount else 0 end) as 'Year Total'
FROM PART AS PART INNER JOIN
CUST_ORDER_LINE AS CUST_ORDER_LINE INNER JOIN
RECEIVABLE_LINE AS RECEIVABLE_LINE INNER JOIN
RECEIVABLE AS RECEIVABLE ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID ON
CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO ON
PART.ID = CUST_ORDER_LINE.PART_ID
WHERE (PART.PRODUCT_CODE = 'TRAILER') AND (PART.ID NOT LIKE 'UPG%') AND (RECEIVABLE_LINE.REFERENCE LIKE '[1-9]%')
AND (RECEIVABLE.CUSTOMER_ID='i69tra')AND PART.USER_1 = 'Dump' AND 'Year Total' != '0.00'
group by PART.DESCRIPTION
HAVING sum(case when year(invoice_date) = @Year then total_amount else 0 end) <> 0
Let me know if you'd like to see how I'd rewrite the query. 😉
December 4, 2009 at 5:33 am
Yes that would be great. I'm new to SQL and trying to learn as much as possible.
December 4, 2009 at 7:12 am
stewsterl 80804 (12/4/2009)
Yes that would be great. I'm new to SQL and trying to learn as much as possible.
With some of the issues I have at times here at work, I'll do this tonight from home.
December 8, 2009 at 9:21 am
How would I change this from SUM to COUNT? I changed it but it gives me total count on every month.
Works good with SUM:
62 IN X 8 LProfile Dump 5K Single Ram0.002525.002810.005160.000.000.000.000.000.000.000.000.0010495.00
7 x 14 14K Dump, Premium TA 4935.000.000.000.000.000.000.000.000.000.000.000.004935.00
7 x 16 Premium Dump 14K11790.000.000.000.000.000.000.000.000.000.000.000.0011790.00
72 IN X 10 Deckover Dump 10K Single Ram0.000.000.000.000.000.000.000.000.002961.400.000.002961.40
72 IN X 10 Deckover Dump 7K Single Ram-2996.000.000.000.000.002519.100.000.000.000.000.000.00-476.90
72 IN X 10 LProfile 10K Dual Ram Dump 0.000.000.000.004099.000.000.000.000.000.000.000.004099.00
80 IN X 12 LProfile 12K Dual Ram Dump0.000.008200.004470.0035000.0012900.000.000.000.000.003870.000.0064440.00
80 IN X 12 LProfile Scissor Dump 12K0.000.004900.000.000.000.000.000.000.000.000.000.004900.00
80 IN X 14 LProfile 14K Dual Ram Dump0.000.000.005170.000.000.000.004800.000.001.000.000.009971.00
80 IN X 14 LProfile 14K Scissor Dump0.005422.000.000.000.000.000.000.000.000.000.000.005422.00
80 IN X 16 LProfile 14K Dual Ram Dump0.000.000.0012098.000.000.000.000.004600.000.000.000.0016698.00
Changed to count gives me this???
6 X 10 Work Series Dump 10K4444444444444
6 X 10 Work Series Dump 7K2222222222222
6 X 12 Work Series Dump 10K2222222222222
6 x 8 Work Series Dump 5K1111111111111
62 IN X 8 LProfile Dump 5K Single Ram4444444444444
7 x 10 10K Standard Dump TA Sprung1111111111111
7 x 12 10K Standard Dump TA Sprung5555555555555
7 x 12 12.5K Dump, Premium TA Slipper4444444444444
7 x 12 12.5K Standard Dump TA Sprung1111111111111
7 x 12 15K Dump, Premium TA Slipper1111111111111
7 x 14 14K Dump, Premium TA 10101010101010101010101010
7 x 14 15K Dump, Premium TA7777777777777
7 x 16 Premium Dump 14K11111111111111111111111111
7 x 16 Tri Axle Premium Dump 15K1111111111111
72 IN X 10 Deckover Dump 10K Single Ram19191919191919191919191919
72 IN X 10 Deckover Dump 7K Single Ram41414141414141414141414141
72 IN X 10 LProfile 10K Dual Ram Dump 1111111111111
72 IN X 10 LProfile Dump Sprg #5000 SH6666666666666
72 IN X 12 LProfile Dump Sprg #60002222222222222
80 IN X 12 LProfile 12K Dual Ram Dump59595959595959595959595959
80 IN X 12 LProfile Dump Sprg #70001111111111111
80 IN X 12 LProfile Scissor Dump 12K15151515151515151515151515
80 IN X 12 LProfile Scissor Dump 14K1111111111111
80 IN X 14 LProfile 14K Dual Ram Dump29292929292929292929292929
80 IN X 14 LProfile 14K Scissor Dump1111111111111
80 IN X 16 LProfile 14K Dual Ram Dump7777777777777
December 8, 2009 at 11:06 am
I promised you how I'd write the query, and here it is:
declare @Year varchar(30)
set @Year = '2009'
select DATEADD(mm, 0, DATEADD(yy, CAST(@Year as int) - 1900, 0))
SELECT
PART.DESCRIPTION,
sum(case when invoice_date >= DATEADD(mm, 0, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 1, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Jan',
sum(case when invoice_date >= DATEADD(mm, 1, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 2, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Feb',
sum(case when invoice_date >= DATEADD(mm, 2, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 3, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Mar',
sum(case when invoice_date >= DATEADD(mm, 3, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 4, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Apr',
sum(case when invoice_date >= DATEADD(mm, 4, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 5, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'May',
sum(case when invoice_date >= DATEADD(mm, 5, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 6, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Jun',
sum(case when invoice_date >= DATEADD(mm, 6, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 7, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Jul',
sum(case when invoice_date >= DATEADD(mm, 7, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 8, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Aug',
sum(case when invoice_date >= DATEADD(mm, 8, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 9, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Sep',
sum(case when invoice_date >= DATEADD(mm, 9, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 10, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Oct',
sum(case when invoice_date >= DATEADD(mm, 10, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 11, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Nov',
sum(case when invoice_date >= DATEADD(mm, 11, DATEADD(yy, CAST(@Year as int) - 1900, 0))
and invoice_date < DATEADD(mm, 12, DATEADD(yy, CAST(@Year as int) - 1900, 0))
then total_amount
else 0
end) as 'Dec',
sum(case when invoice_date >= DATEADD(yy, CAST(@Year as int) - 1900, 0)
and invoice_date < DATEADD(yy, CAST(@Year as int) - 1900 + 1, 0)
then total_amount
else 0
end) as 'Year Total'
FROM
PART AS PART
INNER JOIN CUST_ORDER_LINE AS CUST_ORDER_LINE
ON PART.ID = CUST_ORDER_LINE.PART_ID
INNER JOIN RECEIVABLE_LINE AS RECEIVABLE_LINE
ON (CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID
AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO)
INNER JOIN RECEIVABLE AS RECEIVABLE
ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID
WHERE
(PART.PRODUCT_CODE = 'TRAILER')
AND (PART.ID NOT LIKE 'UPG%')
AND (RECEIVABLE_LINE.REFERENCE LIKE '[1-9]%')
AND (RECEIVABLE.CUSTOMER_ID = 'i69tra')
AND PART.USER_1 = 'Dump'
-- AND 'Year Total' != '0.00' -- The HAVING CLAUSE should handle this condition
GROUP BY
PART.DESCRIPTION
HAVING
sum(case when invoice_date >= DATEADD(yy, CAST(@Year as int) - 1900, 0)
and invoice_date < DATEADD(yy, CAST(@Year as int) - 1900 + 1, 0)
then total_amount
else 0
end) <> 0
;
Now, regarding your second query, not sure. What do you mean by count? Without your code or a description of the tables involved I don't know what you are asking or how to answer it.
December 8, 2009 at 11:49 am
Thanks for the reply.
I Thought if I changed SUM to COUNT I would get the number of items(counts) for each month instead of the totals. but It just gave me the same number for each month.
I was told that I also need a count of the items for each month so that they know how many items were sold.
I figured out how to get the info with this query for year 2009 and month Jan.
select PART.DESCRIPTION, count(total_amount) as orders, YEAR(invoice_date) as [year], MONTH(invoice_date) as [Month]
FROM PART AS PART INNER JOIN
CUST_ORDER_LINE AS CUST_ORDER_LINE INNER JOIN
RECEIVABLE_LINE AS RECEIVABLE_LINE INNER JOIN
RECEIVABLE AS RECEIVABLE ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID ON
CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO ON
PART.ID = CUST_ORDER_LINE.PART_ID
WHERE (PART.PRODUCT_CODE = 'TRAILER') AND (PART.ID NOT LIKE 'UPG%') AND (RECEIVABLE_LINE.REFERENCE LIKE '[1-9]%')
AND (RECEIVABLE.CUSTOMER_ID='i69tra') AND YEAR(invoice_date)='2009' and MONTH(invoice_date)='1'
group by PART.DESCRIPTION, YEAR(invoice_date),MONTH(invoice_date)
I'm sure a cross tab query can do this for each month.
Not sure why changing SUM to COUNT does not work??????
December 8, 2009 at 11:54 am
If you are trying to get a count of the number of items sold for each part, you should sum the qty sold. Is there a qty sold column in the result set?
Again, you haven't provided any details regarding the tables involved so all I can do is guess.
December 8, 2009 at 12:00 pm
Wow! Much cleaner.
I just added this to my new SQL utility belt.
Thanks...
December 8, 2009 at 12:07 pm
lol.. Yup there is a quantity... Duhhh sorry..
December 15, 2009 at 3:08 pm
How would I get the results as MONEY? I've tried.
then '$' + CONVERT(money, total_amount)
But the $ does not show up for some reason.
current results 10495.00
Need $10,495.00
Any ideas on how to do this?
Thanks
December 16, 2009 at 12:57 pm
stewsterl (12/15/2009)
How would I get the results as MONEY? I've tried.then '$' + CONVERT(money, total_amount)
But the $ does not show up for some reason.
current results 10495.00
Need $10,495.00
Any ideas on how to do this?
Thanks
try
then '$' + cast(CONVERT(money, total_amount) as varchar(20))
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 16, 2009 at 2:38 pm
Thanks... That seems to work except I get a conversion error.
TOTAL_AMOUNTdecimal(15, 2)
Conversion failed when converting the varchar value '$2847.00' to data type int.
December 16, 2009 at 3:52 pm
Did you update that same line every time it appeared in the code that Lynn provided?
Somewhere in your process it is trying to convert the $ back to an int.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 1:21 pm
Thanks that was it.....
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply