Complex T-SQL Query
Greetings,
I am trying to do a query in T-SQL. This is what I have thus far listed below:
select
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
from ticket_table
The results of this query:
Alarm Email Letter Other Phone Web
----------- ----------- ----------- ----------- ----------- -----------
8 10 1 4 18 18
There is a field in the ticket_table that is entered_date. Listed below is how I would like this query to appear
Alarm Email Letter Other Phone Web TOTAL
----------- ----------- ----------- ----------- ----------- ----------- -------------
JAN 8 10 1 4 18 18 59
FEB 5 12 5 7 9 8 46
MAR 5 6 7 8 10 9 45
April 12 5 8 10 20 20 64
And how do I prompt for date range in this query?
I am trying to do a query in T-SQL. This is what I have thus far listed below:
select
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
from ticket_table
The results of this query:
Alarm Email Letter Other Phone Web
----------- ----------- ----------- ----------- ----------- -----------
8 10 1 4 18 18
There is a field in the ticket_table that is entered_date. Listed below is how I would like this query to appear
Alarm Email Letter Other Phone Web TOTAL
----------- ----------- ----------- ----------- ----------- ----------- -------------
JAN 8 10 1 4 18 18 59
FEB 5 12 5 7 9 8 46
MAR 5 6 7 8 10 9 45
April 12 5 8 10 20 20 64
And how do I prompt for date range in this query?
select Datepart(month,entered_Dat
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
from ticket_table
where entered_date between '20060101' and getdate()
group by datepart(month,entered_dat
order by month(entered_date)
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
from ticket_table
where entered_date between '20060101' and getdate()
group by datepart(month,entered_dat
order by month(entered_date)
ASKER
How do I get the total for each month and how do I prompt for dates instead of hard coded dates?
Thia should do it.
select Datepart(month,entered_Dat
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
group by datepart(month,entered_dat
order by month(entered_date)
select Datepart(month,entered_Dat
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
group by datepart(month,entered_dat
order by month(entered_date)
You can't prompt for dates per se, but if you stick it in a procedure, or use parameters like this:
DECLARE @StartDate datetime
SET @StartDate = '20060101'
select Datepart(month,entered_Dat
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
count(*) as Total
from ticket_table
where entered_date between @StartDate and getdate()
group by datepart(month,entered_dat
order by month(entered_date)
DECLARE @StartDate datetime
SET @StartDate = '20060101'
select Datepart(month,entered_Dat
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
count(*) as Total
from ticket_table
where entered_date between @StartDate and getdate()
group by datepart(month,entered_dat
order by month(entered_date)
ASKER
Ok so how can I display the months in a format other than 1 , 2, 3, etc..
ASKER
Also with the method you used to total each row is off and incorrect. Do you know how to use the rollup function?
sory use DATENAME instead of DATEPART
select DateNAME(month,entered_Dat
...
group by datename(month,entered_dat
select DateNAME(month,entered_Dat
...
group by datename(month,entered_dat
ASKER
It returned an error when using DATENAME. Invalid column name. what about the total. The total are off. The total should show the results of each row and right now it's not doing that.
Is there another source code value than 1 - 6 for source_code ?
ASKER
There are actually 6 codes. I missed adding that but the total for each row displayed is in correct. Are you familar with the rollup function?
select Datename(mm,entered_Date) as [ ],
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datename(mm,entered_date) with rollup
order by month(entered_date)
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datename(mm,entered_date) with rollup
order by month(entered_date)
ASKER
Ok everything works with the acception of the order by clause. It returns an error that say invalid order by clause.
Select datename(mm,convert(dateti
,Alarms,emails,letters,oth
from (
select month(entered_date) as mno
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by month(entered_date) with rollup
) as x
order by mno
,Alarms,emails,letters,oth
from (
select month(entered_date) as mno
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by month(entered_date) with rollup
) as x
order by mno
ASKER
Got an error. Incorrect syntax near the keyword 'as'. Line 5 incorrect syntax near 'sum'
Yes, but the rollup operator adds the columns not the rows. I don't think this is what you're looking for.
Put order by 1 insted of the column
Put order by 1 insted of the column
select Datename(mm,entered_Date) as [ ],
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datename(mm,entered_date) with rollup
order by 1
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datename(mm,entered_date) with rollup
order by 1
ASKER
Everything is adding up fine but the Months are not in order. The order by 1 places the totals in it's own row at the top of the results instead of the bottom. So the only problem with this query is that the months are not in order.
ASKER
If we use the datepart function then declare the variable the variable in that column like 1=JAN, 2=FEB, etc.. how would you go about implementing this into this query?
select DatePart(mm,entered_date) as Month,
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web,
count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datepart(mm,entered_date)
order by Month
select DatePart(mm,entered_date) as Month,
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web,
count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datepart(mm,entered_date)
order by Month
ASKER
Come on don't give up on me now. This is the last part of this puzzle. You guys are excellent. I just needs your input on the last part of this puzzle which involves placing the months in order. Do you have to place a declare variable in this code? Have any other ideas?
Ahhh, yes, you need them in order of the month not alphabetical. :-)
Off of the top of my head this is the only way to do that:
SELECT Monthm, Alarms, Emails, Letters, Other, Phone, Web, Total
FROM (
select DateName(mm,entered_date) as Month,
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web,
count(*) as Total,
DatePart(mm,entered_date)
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datepart(mm,entered_date)
ORDER BY DatePart(mm,entered_date))
I think this will work, but I don't have anything to test against.
Off of the top of my head this is the only way to do that:
SELECT Monthm, Alarms, Emails, Letters, Other, Phone, Web, Total
FROM (
select DateName(mm,entered_date) as Month,
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web,
count(*) as Total,
DatePart(mm,entered_date)
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datepart(mm,entered_date)
ORDER BY DatePart(mm,entered_date))
I think this will work, but I don't have anything to test against.
And yes, if you want to replace the '20060101' witha variable, you'll want to declare it.
Select datename(mm,convert(dateti
,Alarms,emails,letters,oth
from (
select month(entered_date) as mno
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by month(entered_date) with rollup
) as x
order by coalesce(mno,999)
,Alarms,emails,letters,oth
from (
select month(entered_date) as mno
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by month(entered_date) with rollup
) as x
order by coalesce(mno,999)
ASKER
Listed below is how I would like this query to appear:
Alarm Email Letter Other Phone Web TOTAL
----------- ----------- ----------- ----------- ----------- ----------- -------------
JAN 8 10 1 4 18 18 59
FEB 5 12 5 7 9 8 46
MAR 5 6 7 8 10 9 45
April 12 5 8 10 20 20 64
Alarm Email Letter Other Phone Web TOTAL
----------- ----------- ----------- ----------- ----------- ----------- -------------
JAN 8 10 1 4 18 18 59
FEB 5 12 5 7 9 8 46
MAR 5 6 7 8 10 9 45
April 12 5 8 10 20 20 64
ASKER
I get this error when I run this query:
Select datename(mm,convert(dateti
,Alarms,emails,letters,oth
from (
select month(entered_date) as mno
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by month(entered_date) with rollup
) as x
order by coalesce(mno,999)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'sum'.
Select datename(mm,convert(dateti
,Alarms,emails,letters,oth
from (
select month(entered_date) as mno
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
,count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by month(entered_date) with rollup
) as x
order by coalesce(mno,999)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'sum'.
ASKER
I get this error when I run this query:
SELECT Monthm, Alarms, Emails, Letters, Other, Phone, Web, Total
FROM (
select DateName(mm,entered_date) as Month,
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web,
count(*) as Total,
DatePart(mm,entered_date)
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datepart(mm,entered_date)
ORDER BY DatePart(mm,entered_date))
Server: Msg 1033, Level 15, State 1, Line 16
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
SELECT Monthm, Alarms, Emails, Letters, Other, Phone, Web, Total
FROM (
select DateName(mm,entered_date) as Month,
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web,
count(*) as Total,
DatePart(mm,entered_date)
from ticket_table
where entered_date between '20060101' and getdate()
and source_code between 1 and 6
group by datepart(mm,entered_date)
ORDER BY DatePart(mm,entered_date))
Server: Msg 1033, Level 15, State 1, Line 16
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Fellas,
This is the query that works listed below:
SELECT CASE WHEN DatePart(mm, entered_date) = 1 THEN 'Jan' WHEN DatePart(mm, entered_date) = 2 THEN 'Feb' WHEN DatePart(mm, entered_date)
= 3 THEN 'March' WHEN DatePart(mm, entered_date) = 4 THEN 'April' WHEN DatePart(mm, entered_date) = 5 THEN 'May' WHEN DatePart(mm, entered_date)
= 6 THEN 'June' WHEN DatePart(mm, entered_date) = 7 THEN 'July' WHEN DatePart(mm, entered_date) = 8 THEN 'August' WHEN DatePart(mm, entered_date)
= 9 THEN 'September' WHEN DatePart(mm, entered_date) = 10 THEN 'October' WHEN DatePart(mm, entered_date) = 11 THEN 'November' WHEN DatePart(mm,
entered_date) = 12 THEN 'December' END AS Expr1, SUM(CASE source_code WHEN 6 THEN 1 ELSE 0 END) AS Alarms,
SUM(CASE source_code WHEN 2 THEN 1 ELSE 0 END) AS Emails, SUM(CASE source_code WHEN 4 THEN 1 ELSE 0 END) AS Letters,
SUM(CASE source_code WHEN 5 THEN 1 ELSE 0 END) AS Other, SUM(CASE source_code WHEN 1 THEN 1 ELSE 0 END) AS Phone,
SUM(CASE source_code WHEN 3 THEN 1 ELSE 0 END) AS Web, COUNT(*) AS Total
FROM dbo.ticket
WHERE (entered_date BETWEEN '20060101' AND '20060531') AND (source_code BETWEEN 1 AND 6)
GROUP BY DATEPART(mm, entered_date) WITH ROLLUP
This is the query that works listed below:
SELECT CASE WHEN DatePart(mm, entered_date) = 1 THEN 'Jan' WHEN DatePart(mm, entered_date) = 2 THEN 'Feb' WHEN DatePart(mm, entered_date)
= 3 THEN 'March' WHEN DatePart(mm, entered_date) = 4 THEN 'April' WHEN DatePart(mm, entered_date) = 5 THEN 'May' WHEN DatePart(mm, entered_date)
= 6 THEN 'June' WHEN DatePart(mm, entered_date) = 7 THEN 'July' WHEN DatePart(mm, entered_date) = 8 THEN 'August' WHEN DatePart(mm, entered_date)
= 9 THEN 'September' WHEN DatePart(mm, entered_date) = 10 THEN 'October' WHEN DatePart(mm, entered_date) = 11 THEN 'November' WHEN DatePart(mm,
entered_date) = 12 THEN 'December' END AS Expr1, SUM(CASE source_code WHEN 6 THEN 1 ELSE 0 END) AS Alarms,
SUM(CASE source_code WHEN 2 THEN 1 ELSE 0 END) AS Emails, SUM(CASE source_code WHEN 4 THEN 1 ELSE 0 END) AS Letters,
SUM(CASE source_code WHEN 5 THEN 1 ELSE 0 END) AS Other, SUM(CASE source_code WHEN 1 THEN 1 ELSE 0 END) AS Phone,
SUM(CASE source_code WHEN 3 THEN 1 ELSE 0 END) AS Web, COUNT(*) AS Total
FROM dbo.ticket
WHERE (entered_date BETWEEN '20060101' AND '20060531') AND (source_code BETWEEN 1 AND 6)
GROUP BY DATEPART(mm, entered_date) WITH ROLLUP
sum(case source_code when 6 then 1 else 0 end)as Alarms,
sum(case source_code when 2 then 1 else 0 end)as Emails,
sum(case source_code when 4 then 1 else 0 end)as Letters,
sum(case source_code when 5 then 1 else 0 end)as Other,
sum(case source_code when 1 then 1 else 0 end)as Phone,
sum(case source_code when 3 then 1 else 0 end)as Web
from ticket_table
group by datepart(month,entered_dat
order by month(entered_date)