Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I signed up to your site to get help with a problem and I am so glad I did. I found the help I needed immediately. Thanks to all who contribute to your site..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

count of values received on a weekly basis

thread183-1690315
Read More Threads Like This One
GRP68 (TechnicalUser)
8 Aug 12 16:39
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 (Programmer)
9 Aug 12 2:33

CODE

-- 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 (TechnicalUser)
9 Aug 12 4:18
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 (Programmer)
9 Aug 12 4:33
smile
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 (TechnicalUser)
9 Aug 12 4:38
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 (Programmer)
9 Aug 12 5:02

CODE

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 (TechnicalUser)
9 Aug 12 11:57
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 (Programmer)
9 Aug 12 12:02
Did you tried my suggestion?

Borislav Borissov
VFP9 SP2, SQL Server

r937 (TechnicalUser)
9 Aug 12 13:18

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

GRP68 (TechnicalUser)
9 Aug 12 13:30
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 (Programmer)
9 Aug 12 13:37
Show me what you are tried?
Where you have to convert DATE to int?

Borislav Borissov
VFP9 SP2, SQL Server

GRP68 (TechnicalUser)
9 Aug 12 13:56
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 (Programmer)
9 Aug 12 14:18
try first w/o this code. Try only this:

CODE

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 (TechnicalUser)
9 Aug 12 15:01
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 (Programmer)
9 Aug 12 15:20
What is the type of the field RecvdDate?

Borislav Borissov
VFP9 SP2, SQL Server

bborissov (Programmer)
9 Aug 12 15:25
I should read messages more carefully:

CODE

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 (TechnicalUser)
9 Aug 12 17:17
RecvdDate is the column name for Date in the table HEAT.CallLog

GRP
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close