count of values received on a weekly basis

[GRP68]

I am trying to create a query which will take data from a table that has a date field, code field, reference Number field. Sample table:
reference Code Date
00856147 1 2012-01-03
00856165 1 2012-01-03
00856240 1 2012-01-03
00856338 1 2012-01-03
00856666 2 2012-01-04
00857499 2 2012-01-09
00858151 2 2012-01-10
00858220 2 2012-01-11
00858278 2 2012-01-11
00858563 2 2012-01-12
00858721 2 2012-01-12
00858774 2 2012-01-12
00858861 2 2012-01-12
00859009 2 2012-01-13
00859126 1 2012-01-14

This is what i would like to get: The count of codes that occured within each calendar week:
Code CodeOccurancesWithinWeek WeekBeginning
1 4 2012-01-02
2 1 2012-01-02
2 10 2012-01-09


So far i can do the grouping of the counts but only per day that is listed in the date field. Any suggestions would be appreciated.



GP

 

[bborissov]

-- Preparing Test date, you don't need to do this
DECLARE @Test TABLE (reference char(8), Code int, Dte datetime)

INSERT INTO @Test VALUES ('00856147', 1, '20120103')
INSERT INTO @Test VALUES ('00856165', 1, '20120103')
INSERT INTO @Test VALUES ('00856240', 1, '20120103')
INSERT INTO @Test VALUES ('00856338', 1, '20120103')
INSERT INTO @Test VALUES ('00856666', 2, '20120104')
INSERT INTO @Test VALUES ('00857499', 2, '20120109')
INSERT INTO @Test VALUES ('00858151', 2, '20120110')
INSERT INTO @Test VALUES ('00858220', 2, '20120111')
INSERT INTO @Test VALUES ('00858278', 2, '20120111')
INSERT INTO @Test VALUES ('00858563', 2, '20120112')
INSERT INTO @Test VALUES ('00858721', 2, '20120112')
INSERT INTO @Test VALUES ('00858774', 2, '20120112')
INSERT INTO @Test VALUES ('00858861', 2, '20120112')
INSERT INTO @Test VALUES ('00859009', 2, '20120113')
INSERT INTO @Test VALUES ('00859126', 1, '20120114')
--- End 

--- Set to whatever day you want to be the start if the week.
--- I set it to Monday
SET DATEFIRST 1

SELECT Code,
       COUNT(*) AS CodeOccurancesWithinWeek,
       Dte - DatePart(dw, Dte)+1 AS StartWeek
FROM @Test  -- Change this to your real table name
GROUP BY Code,
         Dte - DatePart(dw, Dte)+1
ORDER BY 3, Code

Borislav Borissov
VFP9 SP2, SQL Server

 

[r937]

next question, boris (and i'm sure you could see this coming) -- how do you get it to show the zero counts?

for instance, what if there are no code 2 counts for a specific week? what if there are no counts at all for a specific week?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Just have one number table and based on it calculate all the weeks you want, then you just need to LEFT JOIN the query above.

Borislav Borissov
VFP9 SP2, SQL Server

 

[r937]

and another table for all possible codes, which you CROSS JOIN with all possible weeks, before doing the LEFT JOIN

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20120101' 
SET @dEnd   = '20120831'

-- Preparing Test date, you don't need to do this
DECLARE @Test TABLE (reference char(8), Code int, Dte datetime)

INSERT INTO @Test VALUES ('00856147', 1, '20120103')
INSERT INTO @Test VALUES ('00856165', 1, '20120103')
INSERT INTO @Test VALUES ('00856240', 1, '20120103')
INSERT INTO @Test VALUES ('00856338', 1, '20120103')
INSERT INTO @Test VALUES ('00856666', 2, '20120104')
INSERT INTO @Test VALUES ('00857499', 2, '20120109')
INSERT INTO @Test VALUES ('00858151', 2, '20120110')
INSERT INTO @Test VALUES ('00858220', 2, '20120111')
INSERT INTO @Test VALUES ('00858278', 2, '20120111')
INSERT INTO @Test VALUES ('00858563', 2, '20120112')
INSERT INTO @Test VALUES ('00858721', 2, '20120112')
INSERT INTO @Test VALUES ('00858774', 2, '20120112')
INSERT INTO @Test VALUES ('00858861', 2, '20120112')
INSERT INTO @Test VALUES ('00859009', 2, '20120113')
INSERT INTO @Test VALUES ('00859126', 1, '20120114')
--- End 

--- Set to whatever day you want to be the start if the week.
--- I set it to Monday
SET DATEFIRST 1

SELECT r.StartWeek,
       Tst.Code,
       Tst.CodeOccurancesWithinWeek
FROM (select DISTINCT (r.range_date - DatePart(dw, r.range_date)+1) AS StartWeek
             from  (select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
                           from       (select 0 b1  union select 1    b1)  t1
                           cross join (select 0 b2  union select 2    b2)  t2
                           cross join (select 0 b3  union select 4    b3)  t3
                           cross join (select 0 b4  union select 8    b4)  t4
                           cross join (select 0 b5  union select 16   b5)  t5
                           cross join (select 0 b6  union select 32   b6)  t6
                           cross join (select 0 b7  union select 64   b7)  t7
                           cross join (select 0 b8  union select 128  b8)  t8
                           cross join (select 0 b9  union select 256  b9)  t9
                           cross join (select 0 b10 union select 512  b10) t10
                           cross join (select 0 b11 union select 1024 b11) t11
                           cross join (select 0 b12 union select 2048 b12) t12
              where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r) r
LEFT JOIN (SELECT Code,
                  COUNT(*) AS CodeOccurancesWithinWeek,
                  Dte - DatePart(dw, Dte)+1 AS StartWeek
           FROM @Test  Test -- Change this to your real table name
           GROUP BY Code,
                    Dte - DatePart(dw, Dte)+1) Tst
ON r.StartWeek = Tst.StartWeek

Borislav Borissov
VFP9 SP2, SQL Server

 

[GRP68]

I guess i must not have been clear enough. Ihave manged to come up witha solution Except it includes any of the dates for the previous sunday with the following weeks data.(I have set it to get the data for a complete year from the present time. I also changed the column name to RecvdDate instead of date to simply make the solution easier to follow)
--Beginning of code
set datefirst 1
Select Code,
convert(varchar, DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, Date), @@DATEFIRST - 1), 102) as WeekBeginning,
Count(Priority) as CodeOccurancesWithinWeek
From dbo.mytable
where Code <= 2 and RecvdDate > DATEADD(wk, -52, GETDATE())
Group by Code, DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, RecvdDate), @@DATEFIRST - 1), YEAR(RecvdDate)
order by Code asc, DATEADD(wk, DATEDIFF(wk, @@DATEFIRST - 1, RecvdDate), @@DATEFIRST - 1), YEAR(RecvdDate)
--End of Code

What happens is that i get the results i am looking for except that it includes any of the codes that occured on the previous Sunday with the following weeks details. This is what i get for my results:

If i use the same table as above but i add a record of
reference Code RecvdDate
00859997 2 2012-01-08

this records somehow gets added to the week of 2012-01-09 count instead of the 2012-01-02 week
Code CodeOccurancesWithinWeek WeekBeginning
1 4 2012-01-02
2 1 2012-01-02
2 11 2012-01-09

Results should be:
Code CodeOccurancesWithinWeek WeekBeginning
1 4 2012-01-02
2 2 2012-01-02
2 10 2012-01-09

 

[bborissov]

Did you tried my suggestion?

Borislav Borissov
VFP9 SP2, SQL Server

 

[r937]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GRP68]

Hi Bborisov,
I tried your solution and got:
Msg 245, Level 16, State 1, Line 7Conversion failed when converting the varchar value '2001-11-22' to data type int.

The date itself is taken right from the my table. I only took a sample of the table to show the type of data.

GRP

 

[bborissov]

Show me what you are tried?
Where you have to convert DATE to int?

Borislav Borissov
VFP9 SP2, SQL Server

 

[GRP68]

Hi Borislav,
(Priority is the column name for code)
Here is what i have. DECLARE @dBegin datetime, @dEnd datetime
SET @dBegin = '20120101'
SET @dEnd = '20120831'

SET DATEFIRST 1

SELECT r.StartWeek,
tst.Priority,
tst.CodeOccurancesWithinWeek
FROM (select DISTINCT (r.range_date - DatePart(dw, r.range_date)+1) AS StartWeek
from (select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from (select 0 b1 union select 1 b1) t1
cross join (select 0 b2 union select 2 b2) t2
cross join (select 0 b3 union select 4 b3) t3
cross join (select 0 b4 union select 8 b4) t4
cross join (select 0 b5 union select 16 b5) t5
cross join (select 0 b6 union select 32 b6) t6
cross join (select 0 b7 union select 64 b7) t7
cross join (select 0 b8 union select 128 b8) t8
cross join (select 0 b9 union select 256 b9) t9
cross join (select 0 b10 union select 512 b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin+b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r) r
LEFT JOIN (SELECT Priority,
COUNT(*) AS CodeOccurancesWithinWeek,
convert(varchar, RecvdDate, 102)- DatePart(dw, RecvdDate)+1 AS StartWeek
FROM Heat.CallLog --@Test Test -- Change this to your real table name
GROUP BY Priority,RecvdDate,
RecvdDate - DatePart(dw, RecvdDate)+1) TST
ON r.StartWeek = TST.StartWeek

 

[bborissov]

try first w/o this code. Try only this:

SET DATEFIRST 1
SELECT Priority,
       COUNT(*) AS CodeOccurancesWithinWeek,
       RecvdDate- DatePart(dw, RecvdDate)+1 AS StartWeek
FROM Heat.CallLog
GROUP BY Priority,
          RecvdDate- DatePart(dw, RecvdDate)+1 
ORDER BY 3, Priority

Borislav Borissov
VFP9 SP2, SQL Server

 

[GRP68]

Here is what i got

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '2001-11-22' to data type int.

GRP

 

[bborissov]

What is the type of the field RecvdDate?

Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

I should read messages more carefully:

SET DATEFIRST 1
SELECT Priority,
       COUNT(*) AS CodeOccurancesWithinWeek,
       DateAdd(dd,- DatePart(dw, RecvdDate)+1,RecvdDate) AS StartWeek
FROM Heat.CallLog
GROUP BY Priority,
          DateAdd(dd,- DatePart(dw, RecvdDate)+1,RecvdDate)
ORDER BY 3, Priority

Borislav Borissov
VFP9 SP2, SQL Server

 

[GRP68]

RecvdDate is the column name for Date in the table HEAT.CallLog

GRP