Week split up for any month - 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

Week split up for any month

Author

Topic

Bhanu_2007
Starting Member

10 Posts

Posted - 2007-11-05 : 01:57:57

Hi frnds,

Could u pls help me in solving this:

I have a table where i store the Daily details of the stock ( Columns:

Id, Name,Qty,Date)..here in this table it has been populated for the entire year.

I have to get the Sum of the Qty for the Week1,Week2,Week3,Week4,Week5 for all the Stocks(stockid) for any Month given as input.

Pls help me out.

Thanks in Advance
Banu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 02:00:14

can you post your table DDL, sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Bhanu_2007
Starting Member

10 Posts

Posted - 2007-11-05 : 03:07:41

ID | Name | Qty | Date(mm/dd/yyyy)
st001| Wuuu | 18 | 01/02/2006
st001 | Wuuu | 20 | 01/03/2006
st001| Wuuu | 18 | 01/04/2006
st001 | Wuuu | 20 | 01/05/2006
st001 | Wuuu | 18 | 01/06/2006
st001 | Wuuu | 20 | 01/09/2006
st001 | Wuuu | 18 | 01/10/2006
st001 | Wxxx | 20 | 01/02/2006
st002 | Wxxx | 40 | 01/03/2006
st002 |Wxxx | 15 | 01/04/2006
st002 | Wxxx | 40 | 01/05/2006
st002 | Wxxx | 15 | 01/06/2006
st002 | Wxxx | 40 | 01/09/2006
st002 | Wxxx | 15 | 01/10/2006
st002 | Wxxx | 40 | 01/11/2006
st002 | Wxxx | 15 | 01/12/2006

Similary there are n no. of stocks populated for the entire year

Input : YearMonth- 200601

The required Output

ID |Name|Week1| Week2 |Week3 |Week4 | Week5
st001 | Wuuu|94|38| - |- | - | -|-
st002 | Wxxx | 140 | 110 | - | - | -
--
Thanks
Bhanu

Bhanu_2007
Starting Member

10 Posts

Posted - 2007-11-05 : 03:48:03

Kindly, help me out....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 04:28:17

[code]DECLARE @sample TABLE
(
[ID] varchar(5),
[Name] varchar(5),
Qty int,
[Date] datetime
)
SET dateformat mdy
INSERT INTO @sample
SELECT 'st001', 'Wuuu', 18, '01/02/2006' UNION ALL
SELECT 'st001', 'Wuuu', 20, '01/03/2006' UNION ALL
SELECT 'st001', 'Wuuu', 18, '01/04/2006' UNION ALL
SELECT 'st001', 'Wuuu', 20, '01/05/2006' UNION ALL
SELECT 'st001', 'Wuuu', 18, '01/06/2006' UNION ALL
SELECT 'st001', 'Wuuu', 20, '01/09/2006' UNION ALL
SELECT 'st001', 'Wuuu', 18, '01/10/2006' UNION ALL
SELECT 'st001', 'Wxxx', 20, '01/02/2006' UNION ALL
SELECT 'st002', 'Wxxx', 40, '01/03/2006' UNION ALL
SELECT 'st002', 'Wxxx', 15, '01/04/2006' UNION ALL
SELECT 'st002', 'Wxxx', 40, '01/05/2006' UNION ALL
SELECT 'st002', 'Wxxx', 15, '01/06/2006' UNION ALL
SELECT 'st002', 'Wxxx', 40, '01/09/2006' UNION ALL
SELECT 'st002', 'Wxxx', 15, '01/10/2006' UNION ALL
SELECT 'st002', 'Wxxx', 40, '01/11/2006' UNION ALL
SELECT 'st002', 'Wxxx', 15, '01/12/2006'

DECLARE
@input_date datetime

SELECT @input_date = '20060120'

SELECT [ID],
SUM(CASE WHEN week_no = 1 THEN Qty ELSE 0 END) AS week1,
SUM(CASE WHEN week_no = 2 THEN Qty ELSE 0 END) AS week2,
SUM(CASE WHEN week_no = 3 THEN Qty ELSE 0 END) AS week3,
SUM(CASE WHEN week_no = 4 THEN Qty ELSE 0 END) AS week4,
SUM(CASE WHEN week_no = 5 THEN Qty ELSE 0 END) AS week5
FROM
(
SELECT [ID],
week_no = DATEPART(week, [Date]) - DATEPART(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)) + 1,
Qty
FROM @sample
WHERE [Date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)
AND [Date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date) + 1, 0)
) d
GROUP BY [ID]

/*
ID week1 week2 week3 week4 week5
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Bhanu_2007
Starting Member

10 Posts

Posted - 2007-11-05 : 04:58:56

Thanks a lot 'K H Tan'...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 05:28:41

Also read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Bhanu_2007
Starting Member

10 Posts

Posted - 2007-11-05 : 05:52:30

The data obtained has to be inserted into a Table..
Is it possible to do with Bulk Insert?
Could u plz guide me?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 07:09:18

quote:
Originally posted by Bhanu_2007

The data obtained has to be inserted into a Table..
Is it possible to do with Bulk Insert?
Could u plz guide me?

Insert into target_table(columns)
select culumns from
(
your cross tab query
) as t

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 07:26:59