SQL statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL statement

Author

Topic

Henrik
Starting Member

5 Posts

Posted - 2007-11-06 : 14:08:55

Hi there, anyone who can help me?

Have a table (salestat) with 3 columns. InvDate, ItemID, ShipQty. Here's a few records...

InvDate ItemID ShipQty
02-01-2005 111000 kr 1,00
02-01-2005 118016 kr 1,00
03-01-2005 111076 kr 1,00
03-01-2005 118006 kr 1,00
03-01-2005 118008 kr 1,00
03-01-2005 111000 kr 1,00
03-01-2005 111028 kr 1,00
03-01-2005 115002 kr 1,00
03-01-2005 118008 kr 1,00

Now I'd like to make a query for any month showing how many Items we have sold (grouped by ItemID). But I can't make it work.

Any ideas?

Thanks in advance...

Henrik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 14:15:06

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', InvDate), '19000101'),
ItemID, SUM(ShipQty) AS Qty
FROM SaleStat
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', InvDate), '19000101')

E 12°55'05.25"
N 56°04'39.16"

Henrik
Starting Member

5 Posts

Posted - 2007-11-06 : 14:38:12

Thanks a lot. I still can't get my head around it though. I'm a novice :-) Let's say I like to know sale of distinct items in january 2005. I thought it would be something like;

select ItemID, sum(ShipQty)from salestat where InvDate is between '01-01-2005' and '31-01-2005' group by ItemID order by ItemID;

But it doesn't work...!!! Why?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 14:42:29

it should work.
are you by chance NOT using datetime datatype for Invdate column?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 14:47:14

select ItemID, sum(ShipQty)from salestat where InvDate is between '01-01-2005' and '31-01-2005' group by ItemID order by ItemID

E 12°55'05.25"
N 56°04'39.16"

Henrik
Starting Member

5 Posts

Posted - 2007-11-06 : 15:00:19

This one worked... Thanks...

select ItemID, sum(ShipQty) from salgsstat where InvDate between #1/1/2005# and #1/31/2005# group by ItemID order by ItemID;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 15:05:01

Are you using Microsoft Access?

E 12°55'05.25"
N 56°04'39.16"

Henrik
Starting Member

5 Posts

Posted - 2007-11-06 : 15:12:06

yes - have linked a sql server database to Access...

Henrik
Starting Member

5 Posts

Posted - 2007-11-06 : 15:15:15

Do you by any chance know how to group by months or quarters? Let's say I like to know how many of one particular item we have sold per quarter? I.e. 111002. Imagine to get a result like following:

1qua05 110
2qua05 230
1qua06 450 etc...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 15:27:45

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', InvDate), '19000101'),
ItemID, SUM(ShipQty) AS Qty
FROM SaleStat
GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', InvDate), '19000101')

E 12°55'05.25"
N 56°04'39.16"

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-07 : 04:37:01

u can use Datepart function to fetch the month part of your invdate and compare this value to specific month no like
monthno_desired=datepart(Mm,field_name)
and then make a group by
on the basis of ID

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources