Daily,Weekly,Monthly Report using TSQL

Question

Post reply

Daily,Weekly,Monthly Report using TSQL

Agile

Ten Centuries

Points: 1153
More actions
May 5, 2010 at 7:23 pm

#100251

Need help on this one guys and girls, I manage to create a SQL statement for daily, weekly and monthly report but they are not subquery.
Here is my code:
--Daily
SELECT Department, COUNT(EmpID) AS 'Daily' FROM tblNGESDData
WHERE TDate = CONVERT(nvarchar(10),GETDATE(),101)
GROUP BY Dept
--Weekly
SELECT Department, COUNT(EmpID) AS 'Weekly' FROM tblNGESDData
WHERE TDate BETWEEN DATEADD(d,-7,CONVERT(nvarchar(10),GETDATE(),101)) AND CONVERT(nvarchar(10),GETDATE(),101)
GROUP BY Dept
--Monthly
SELECT Department, COUNT(EmpID) AS 'Monthly' FROM tblNGESDData
WHERE TDate BETWEEN DATEADD(d,-30,CONVERT(nvarchar(10),GETDATE(),101)) AND CONVERT(nvarchar(10),GETDATE(),101)
GROUP BY Dept
What I want to do is that I want to create a View that will show all these report using subquery or other way. Here is the sample output:
Department | Daily | Weekly | Monthly
Admin | 1 | 3 | 4
Purchasing | 0 | 1 | 1
HR | 0 | 0 | 0
Thank you in advance
Thanks,
Morris

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Can you confirm the version of SQL you're running?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Agile Ten Centuries Points: 1153 More actions · Answer 2

Sorry for not mentioning the version, I'm using SQL Server 2000

Thanks,
Morris

Sergiy SSC Guru Points: 110208 More actions · Answer 3

SELECT Department, DATEADD(dd, DATEDIFF(dd, 0, TDate ), 0) DayStart, COUNT(EmpID) [Daily]

FROM tblNGESDData

GROUP BY Department, DATEADD(dd, DATEDIFF(dd, 0, TDate ), 0)

--Weekly

SELECT Department, DATEADD(wk, DATEDIFF(wk, 0, TDate ), 0) WeekStart, COUNT(EmpID) [Weekly]

FROM tblNGESDData

GROUP BY Department, DATEADD(wk, DATEDIFF(wk, 0, TDate ), 0)

--Monthly

SELECT Department, DATEADD(mm, DATEDIFF(mm, 0, TDate ), 0) MonthStart, COUNT(EmpID) [Monthly]

FROM tblNGESDData

GROUP BY Department, DATEADD(mm, DATEDIFF(mm, 0, TDate ), 0)

If you make it views then you can select any day/week/month from the history to report.

_____________
Code for TallyGenerator

Agile Ten Centuries Points: 1153 More actions · Answer 4

Thanks for reply, As I said I already did the query(T-SQL) to get the Daily, Weekly, Report, But I need them in "1" view so maybe I need to use subquery here or something that would create all these query into "1" view

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Agile (5/6/2010)
Thanks for reply, As I said I already did the query(T-SQL) to get the Daily, Weekly, Report, But I need them in "1" view so maybe I need to use subquery here or something that would create all these query into "1" view

So do a UNION ALL between the 3 queries and either a Cross Tab or a Pivot to rotate them up to the same line for each department.

Also, the reason why Sergiy posted code like he did is because his code will be MUCH faster than yours because he didn't use CONVERT like you did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Agile Ten Centuries Points: 1153 More actions · Answer 6

Thank you for your reply, sorry for not acknowledging his codes , yes your right it's much faster compare to my codes, but still my problem is that how will I convert them into 1 query? because i want them in 1 view. I need help in revising the codes and transform them into 1 query or view. Because the current situation is that I have 3 seperated query.

Thanks,
Morris

Sergiy SSC Guru Points: 110208 More actions · Answer 7

How desired result should look like?

_____________
Code for TallyGenerator

Agile Ten Centuries Points: 1153 More actions · Answer 8

Thank you for reply.., This will be the desired output.

CREATE TABLE [dbo].[tblESDData] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[Dept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Daily] [int] NULL ,

[Weekly] [int] NULL ,

[Monthly] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO tblESDData (Dept,Daily,Weekly,Monthly)VALUES('SMT',1,3,4)

INSERT INTO tblESDData (Dept,Daily,Weekly,Monthly)VALUES('MAIN',0,4,5)

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Agile (5/6/2010)
Thank you for your reply, sorry for not acknowledging his codes , yes your right it's much faster compare to my codes, but still my problem is that how will I convert them into 1 query? because i want them in 1 view. I need help in revising the codes and transform them into 1 query or view. Because the current situation is that I have 3 seperated query.

Ummmm.... I already told you in my previous post. Use UNION ALL to combine the output of the 3 queries and then pivot the result using either a Cross Tab or a Pivot. If you want actual code to see how that's done, you need to provide some readily consumable data. Please see the article at the first link in my signature line below for how to do that properly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Agile Ten Centuries Points: 1153 More actions · Answer 10

This is the real data that I used.

CREATE TABLE [dbo].[tblData] (

[LogID] [int] IDENTITY (1, 1) NOT NULL ,

[EmpID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[EmpName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Dept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[TDate] [datetime] NULL

) ON [PRIMARY]

GO

INSERT INTO tblData (EmpID,EmpName,Dept,TDate)VALUES('0111','A','SMT','5/4/2010')

INSERT INTO tblData (EmpID,EmpName,Dept,TDate)VALUES('0111','A','SMT','5/8/2010')

INSERT INTO tblData (EmpID,EmpName,Dept,TDate)VALUES('0222','B','MAIN','5/6/2010')

INSERT INTO tblData (EmpID,EmpName,Dept,TDate)VALUES('0333','C','MAIN','5/6/2010')

INSERT INTO tblData (EmpID,EmpName,Dept,TDate)VALUES('0444','D','MAIN','4/27/2010')

INSERT INTO tblData (EmpID,EmpName,Dept,TDate)VALUES('0555','E','SMT','5/1/2010')

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Thanks. That's just what I needed.

Like I said, UNION ALL the 3 queries and do a simple Cross Tab... this will do what you want it to...

SELECT preagg.Dept,

MAX(CASE WHEN preagg.PeriodType = 'D' THEN preagg.PeriodValue ELSE 0 END) AS Daily,

MAX(CASE WHEN preagg.PeriodType = 'W' THEN preagg.PeriodValue ELSE 0 END) AS Weekly,

MAX(CASE WHEN preagg.PeriodType = 'M' THEN preagg.PeriodValue ELSE 0 END) AS Monthly

FROM (

SELECT Dept, COUNT(*) AS PeriodValue, 'M' AS PeriodType

FROM dbo.tblData

WHERE TDate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-30

AND tDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

GROUP BY Dept

UNION ALL

SELECT Dept, COUNT(*) AS PeriodValue, 'W' AS PeriodType

FROM dbo.tblData

WHERE TDate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-7

AND tDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

GROUP BY Dept

UNION ALL

SELECT Dept, COUNT(*) AS PeriodValue, 'D' AS PeriodType

FROM dbo.tblData

WHERE TDate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-1

AND tDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

GROUP BY Dept

)preagg

GROUP BY preagg.Dept

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Agile Ten Centuries Points: 1153 More actions · Answer 12

Thank you very much! It's 100% solved my problem. I tried to figure it out by myself by using union all but I'm not succesful. Your code was perfect. Thank you again.

Thanks,
Morris

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

Agile (5/8/2010)
Thank you very much! It's 100% solved my problem. I tried to figure it out by myself by using union all but I'm not succesful. Your code was perfect. Thank you again.

You're very welcome. I very much appreciate the feedback.

As a sidebar, this is a good example of why some of us are such PITA's when it comes for asking posters to provide readily consumable data... we don't always (don't usually) have the time to setup test data and some of us don't like to post code unless we've tested it. As soon as you posted the table creation and data inserts, then I had the time to lend a hand. Thanks for doing that because it was actually a bit of a fun problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)