Want to group by 1st and 2nd half of the month

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)

Want to group by 1st and 2nd half of the month

Author

Topic

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 09:23:14

I want below data to be shown

12/01/10 00:00:00 12
13/01/10 00:00:00 8
05/02/10 00:00:00 14
12/03/10 00:00:00 15
25/02/10 00:00:00 14
30/03/10 00:00:00 13
31/03/10 00:00:00 0

in this way, Can anybody help me in achieving this.

13/01/10 00:00:00 20
05/02/10 00:00:00 14
12/03/10 00:00:00 15
25/02/10 00:00:00 14
31/03/10 00:00:00 13

Thanks in advance

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-21 : 09:45:29

Not sure what your asking for. You do a rollup of 01/10 into one output row, you do NOT rollup 02/10 (2 output rows) but you do rollup 03/10 into one output row. How do you define 1st half and second half of a month? What are you truly trying to accomplish?

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 09:47:00

What i understand is
You sumup the qty or units between 1 to 15 and 15th to end of month.
and show the aggregate value against the last day for 15th or end of month accordingly.

Is my undestanding correct ?

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 09:50:21

Hi Terry,

I want to sum all the values before and after 15th of every month.

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 09:56:24

In the example below

12/01/10 00:00:00 12
13/01/10 00:00:00 8
05/02/10 00:00:00 14
12/03/10 00:00:00 15
25/02/10 00:00:00 14
30/03/10 00:00:00 13
31/03/10 00:00:00 0

these two records are recorded before 15th of January, so I need to Sum the values 12 and 8, which is 20 and display the recent date in the category.

12/01/10 00:00:00 12
13/01/10 00:00:00 8

Similarly,

30/03/10 00:00:00 13
31/03/10 00:00:00 0

The above two records are recorded after 15th of March, So I need to sum the values 13 and 0.

Hope this should help you in understanding my question.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-21 : 09:58:12

[code]
declare @data table
(
dte datetime,
val int
)
set dateformat dmy
insert into @data
select '02/01/10 00:00:00', 12 union all
select '13/01/10 00:00:00', 8 union all
select '05/02/10 00:00:00', 14 union all
select '12/03/10 00:00:00', 15 union all
select '25/02/10 00:00:00', 14 union all
select '30/03/10 00:00:00', 13 union all
select '31/03/10 00:00:00', 0

-- Query
; with cte
as
(
select *,
date_grp = dateadd(month, datediff(month, 0, dte), case when day(dte) <= 15 then 0 else 15 end)
from @data
)
select dte, sumval
from
(
select *,
sumval = sum(val) over (partition by date_grp),
row_no = row_number() over (partition by date_grp order by dte desc)
from cte
) c
where row_no = 1
order by dte

/*
dte sumval
------------------------------------------------------ -----------
2010-01-13 00:00:00.000 20
2010-02-05 00:00:00.000 14
2010-02-25 00:00:00.000 14
2010-03-12 00:00:00.000 15
2010-03-31 00:00:00.000 13

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

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

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 10:02:10

Below is the query :-

SELECT CAST(CONVERT(nvarchar(10), comp.dTime, 111) AS DateTime) AS checkedDate, BaitMothFlyUnitsLocations.Area,
SUM((CASE det.sValue WHEN '1' THEN 0 WHEN '0' THEN 1 ELSE 0 END)) AS BaitsActivity
FROM TblCheckListCompletion AS comp LEFT OUTER JOIN
TblCompletionDetails AS det ON comp.iCompletionID = det.iCompletionID INNER JOIN
TblCompletionDetails AS det1 ON comp.iCompletionID = det1.iCompletionID INNER JOIN
BaitMothFlyUnitsLocations ON det1.sValue = BaitMothFlyUnitsLocations.LocationID COLLATE Latin1_General_CI_AS INNER JOIN
TblCheckListItems AS items ON det.iCheckItemID = items.iCheckItemID
WHERE (comp.iCheckListID = 96) AND (det1.sValue LIKE 'BP%') AND (BaitMothFlyUnitsLocations.Area = 'Stores - Finished Goods') AND
(items.sDescription <> 'Location ID') AND (items.sDescription <> 'Comments') AND (det1.dTime BETWEEN CONVERT(DATETIME, CONVERT(INT,
@dFrom)) AND CONVERT(DATETIME, CONVERT(INT, @dTo)))
GROUP BY CAST(CONVERT(nvarchar(10), comp.dTime, 111) AS DateTime), BaitMothFlyUnitsLocations.Area

I am using to pull this record

12/01/10 00:00:00 12
13/01/10 00:00:00 8
05/02/10 00:00:00 14
12/03/10 00:00:00 15
25/02/10 00:00:00 14
30/03/10 00:00:00 13
31/03/10 00:00:00 0

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 10:13:22

Hi KH,

I tried following your solution, however it's giving me an error about Group by clause.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-21 : 10:17:11

quote:
Originally posted by cutiepie252010

Hi KH,

I tried following your solution, however it's giving me an error about Group by clause.

Thanks

post your query here

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

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 10:18:30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-21 : 10:21:41

quote:
Originally posted by cutiepie252010

Below is the query :-

SELECT CAST(CONVERT(nvarchar(10), comp.dTime, 111) AS DateTime) AS checkedDate, BaitMothFlyUnitsLocations.Area,
SUM((CASE det.sValue WHEN '1' THEN 0 WHEN '0' THEN 1 ELSE 0 END)) AS BaitsActivity
FROM TblCheckListCompletion AS comp LEFT OUTER JOIN
TblCompletionDetails AS det ON comp.iCompletionID = det.iCompletionID INNER JOIN
TblCompletionDetails AS det1 ON comp.iCompletionID = det1.iCompletionID INNER JOIN
BaitMothFlyUnitsLocations ON det1.sValue = BaitMothFlyUnitsLocations.LocationID COLLATE Latin1_General_CI_AS INNER JOIN
TblCheckListItems AS items ON det.iCheckItemID = items.iCheckItemID
WHERE (comp.iCheckListID = 96) AND (det1.sValue LIKE 'BP%') AND (BaitMothFlyUnitsLocations.Area = 'Stores - Finished Goods') AND
(items.sDescription <> 'Location ID') AND (items.sDescription <> 'Comments') AND (det1.dTime BETWEEN CONVERT(DATETIME, CONVERT(INT,
@dFrom)) AND CONVERT(DATETIME, CONVERT(INT, @dTo)))
GROUP BY CAST(CONVERT(nvarchar(10), comp.dTime, 111) AS DateTime), BaitMothFlyUnitsLocations.Area

what is this query ? is it the "I tried following your solution, however it's giving me an error about Group by clause." ?
I don't see how you follow my solution in that query

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

cutiepie252010
Starting Member

7 Posts

Posted - 2010-04-21 : 10:44:35

Hi KH,

It seems to be working now.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-21 : 11:05:51

cheers