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 2005 Forums
 Transact-SQL (2005)
 Problem writing a CASE statement

Author  Topic 

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 09:43:02
I'm am trying to write a query in MS Query (Excel 2007) which in turn is accessing a pass through query from Access 2007 to a SQL Express 2005 table.

The table contains normalized data of our month end inventory counts. I'm trying to use Jeff Smith's (http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/06/12/60230.aspx) "Using GROUP BY to avoid self-joins" article as a guide to create a query which will allow me to compare inventories from two user-selected months in a cross-tab format. However, I can't even get the query to function with the dates hard coded into the query, much less with user entered parameters.

Right or wrong, here's what I have so far:

SELECT 
Inventory.Itemkey, 
Inventory.Invdate,
SUM(CASE Invdate WHEN Inventory.Invdate BETWEEN #4/1/2009# AND #4/30/2009# THEN Inventory.Onhandqty ELSE 0 end) AS Onhandqty1,
SUM(CASE Invdate WHEN Inventory.Invdate BETWEEN #4/1/2009# AND #4/30/2009# THEN Inventory.Unitcost ELSE 0 end) AS Avgunitcost1,
Onhandqty1 * Avgunitcost1 AS Extendedcost1
SUM(CASE Invdate WHEN Inventory.Invdate BETWEEN #3/1/2009# AND #3/31/2009# THEN Inventory.Onhandqty ELSE 0 end) AS Onhandqty2,
SUM(CASE Invdate WHEN Inventory.Invdate BETWEEN #3/1/2009# AND #3/31/2009# THEN Inventory.Unitcost ELSE 0 end) AS Avgunitcost2,
Onhandqty2 * Avgunitcost2 AS Extendedcost2
Extendedcost1 - Extendedcost2 AS VarianceCost
FROM F:\Inventory.accdb Inventory.accdb AS Inventory


Running the query gives me an Invalid Column Expression followed by the first SUM(CASE) statement.

The data contained in the table is fairly simple:
ITEMKEY, LOCATION, SITEID, INVDATE, ONHANDQTY and UNITCOST, with SITEID being a string containing the facility name and LOCATION being a string containing the inventory location (bin).

Sample data:
Itemkey Invdate Onhandqty Avgunitcost
A000101 12/31/08 127 1.23
A000104 12/31/08 21 7.50
A000108 12/31/08 10 4.29
A000101 01/31/09 123 1.23
A000104 01/31/09 20 8.00
A000110 01/31/09 55 1.00

Expected results:
Item Key Onhandqty1 Avgunitcost1 Extendedcost1 Onhandqty2 Avgunitcost 2 Extendedcost2 Variancecost Variancepercent
A000101 123 1.23 151.29 127 1.23 156.21 -4.92 -0.0325
A000104 20 8.00 160.00 21 7.50 157.50 2.50 0.0156
A000108 __ ____ _____ 10 4.29 42.90 -42.90 -1.0000
A000110 55 1.00 55.00 __ ____ _____ 55.00 1.0000

Obviously, I would need to build the Variancepercent formula to give me the +/- 1.0000 in the event either Extendedcost is null or zero.

I've tried getting help from a couple of Excel forums, but so far, no luck and I haven't seen any example "how to" articles which use BETWEEN in a CASE statement, so I don't even know for certain that it works in that context. Any help would be appreciated!

Steve Latta
Easley, SC USA






"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-06-09 : 09:47:00
Maybe this is your problem:
CASE Invdate WHEN Inventory.Invdate BETWEEN #4/1/2009# AND #4/30/2009# THEN Inventory.Onhandqty ELSE 0 end

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-09 : 12:45:06
[code]

SELECT Itemkey,
SUM(CASE WHEN Seq=1 THEN Onhandqty ELSE 0 END) AS Onhandqty1,
SUM(CASE WHEN Seq=1 THEN Avgunitcost ELSE 0 END) AS Avgunitcost1,
SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost ELSE 0 END) AS Extendedcost1,
SUM(CASE WHEN Seq=2 THEN Onhandqty ELSE 0 END) AS Onhandqty2,
SUM(CASE WHEN Seq=2 THEN Avgunitcost ELSE 0 END) AS Avgunitcost2,
SUM(CASE WHEN Seq=2 THEN Onhandqty * Avgunitcost ELSE 0 END) AS Extendedcost2,
SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost WHEN Seq=2 THEN (-1)*Onhandqty * Avgunitcost ELSE 0 END) AS Variancecost,
SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost WHEN Seq=2 THEN (-1)*Onhandqty * Avgunitcost ELSE 0 END)*1.0/SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost ELSE 0 END) AS variancepercent
FROM
(
SELECT
Inventory.Itemkey,
Inventory.Invdate,Inventory.Onhandqty, Inventory.Avgunitcost,
ROW_NUMBER() OVER (PARTITION BY Inventory.Itemkey ORDER BY Inventory.Invdate) AS Seq
FROM F:\Inventory.accdb Inventory.accdb AS Inventory
)t
GROUP BY Itemkey

[/code]
Go to Top of Page

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 13:48:40
Thanks for the responses!

Webfred: That's certainly not my only problem, but I think you're correct that I don't need to put anything between the words CASE and WHEN as long as a data field follows the WHEN.

Visakh16: Looking at that query, it appears that it is sorting by Invdate and then pulling the 1st and 2nd items in the sequence, regardless of the dates. There is a possibility of any item having no record for a given period (if inventory is reduced to zero in the period, for example). Using this query, if the data is sorted in decending order by Invdate, I would get two most recent lines where data does exist, rather than returning all the results including nulls for the 2 most recent periods.


"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-09 : 13:51:02
quote:
Originally posted by SBLatta

Thanks for the responses!

Webfred: That's certainly not my only problem, but I think you're correct that I don't need to put anything between the words CASE and WHEN as long as a data field follows the WHEN.

Visakh16: Looking at that query, it appears that it is sorting by Invdate and then pulling the 1st and 2nd items in the sequence, regardless of the dates. There is a possibility of any item having no record for a given period (if inventory is reduced to zero in the period, for example). Using this query, if the data is sorted in decending order by Invdate, I would get two most recent lines where data does exist, rather than returning all the results including nulls for the 2 most recent periods.

so what you're interested is always data between a given date range?
"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 14:02:54
quote:
Originally posted by visakh16
so what you're interested is always data between a given date range?


Yes. I want to eventually get it to the point where I can put two month end dates into two cells in Excel and pass those to the query as Parameters, calculating the month start dates from those. That will give me two monthly date ranges, which do not have to be one right after the other, for which I can compare data. For instance, I could compare 01 Apr 2009 - 30 Apr 2009 to 01 Mar 2009 - 31 Mar 2009 just as easily as I could compare 01 Apr 2009 - 30 Apr 2009 to 01 Apr 2008 - 30 Apr 2008 using the same query.

I'll work on getting the parameters in later. For now, I'd be happy to get this query running with the date ranges hard coded into the query.

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-09 : 14:10:14
so in that case you want two values to ones corresponding to latest and earliest date?
Go to Top of Page

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 14:17:22
quote:
Originally posted by visakh16

so in that case you want two values to ones corresponding to latest and earliest date?


I think that is correct. For any instance where one of the date ranges returns null values, the nulls should ideally be converted to zeroes.

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-09 : 14:19:04
one of date ranges? sorry i'm confused...so you will have two date ranges? i.e four date values?
Go to Top of Page

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 14:34:55
quote:
Originally posted by visakh16

one of date ranges? sorry i'm confused...so you will have two date ranges? i.e four date values?


Yes, there will be two date ranges for a total of 4 dates From my code at in the first post:


SUM(CASE Invdate WHEN Inventory.Invdate BETWEEN #4/1/2009# AND #4/30/2009# THEN Inventory.Onhandqty ELSE 0 end) AS Onhandqty1
SUM(CASE Invdate WHEN Inventory.Invdate BETWEEN #3/1/2009# AND #3/31/2009# THEN Inventory.Onhandqty ELSE 0 end) AS Onhandqty2


If the date is in the more recent range, Onhandqty is put into Onhandqty1. If it is in the older range, Onhandqty is put into Onhandqty2. Dates are in U.S. format (mm/dd/yyyy).

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-09 : 14:42:19
then isnt this be enough?

SELECT Itemkey,
SUM(CASE WHEN Seq=1 THEN Onhandqty ELSE 0 END) AS Onhandqty1,
SUM(CASE WHEN Seq=1 THEN Avgunitcost ELSE 0 END) AS Avgunitcost1,
SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost  ELSE 0 END) AS Extendedcost1,
SUM(CASE WHEN Seq=2 THEN Onhandqty ELSE 0 END) AS Onhandqty2,
SUM(CASE WHEN Seq=2 THEN Avgunitcost ELSE 0 END) AS Avgunitcost2,
SUM(CASE WHEN Seq=2 THEN Onhandqty * Avgunitcost  ELSE 0 END) AS Extendedcost2,
SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost WHEN Seq=2 THEN (-1)*Onhandqty * Avgunitcost ELSE 0 END) AS Variancecost,
SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost WHEN Seq=2 THEN (-1)*Onhandqty * Avgunitcost ELSE 0 END)*1.0/SUM(CASE WHEN Seq=1 THEN Onhandqty * Avgunitcost  ELSE 0 END) AS variancepercent
FROM
(
SELECT 
Inventory.Itemkey, 
Inventory.Invdate,Inventory.Onhandqty, Inventory.Avgunitcost,
CASE WHEN  Inventory.Invdate BETWEEN @StartDate1 AND @EndDate1 THEN 1
ELSE 2
END AS Seq
FROM F:\Inventory.accdb Inventory.accdb AS Inventory
WHERE ((Inventory.Invdate BETWEEN @StartDate1 AND @EndDate1 )
OR (Inventory.Invdate BETWEEN @StartDate2 AND @EndDate2))
)t
GROUP BY Itemkey
Go to Top of Page

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 15:05:35
I have to move on to something else for the rest of the day. I will try this and reply tomorrow.

Thanks again for the help!

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

SBLatta
Starting Member

33 Posts
Posted - 2009-06-09 : 15:59:56
quote:
Originally posted by visakh16

SELECT 
Inventory.Itemkey, 
Inventory.Invdate,Inventory.Onhandqty, Inventory.Avgunitcost,
CASE WHEN  Inventory.Invdate BETWEEN @StartDate1 AND @EndDate1 THEN 1
ELSE 2
END AS Seq
FROM F:\Inventory.accdb Inventory.accdb AS Inventory
WHERE ((Inventory.Invdate BETWEEN @StartDate1 AND @EndDate1 )
OR (Inventory.Invdate BETWEEN @StartDate2 AND @EndDate2))



I'm back. I tried the whole query and got an error, so I tried just the above portion to see if I could isolate the problem. Excel (MS Query) returned "Didn't expect 'Inventory.Invdate' after the SELECT column list."

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-10 : 14:28:28
i dont know about ms query in excel...however above works fine for me in query analyser
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site