Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Complex T-SQL Query

Posted on 2006-05-18
28
Medium Priority
?
216 Views
Last Modified: 2009-12-16
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?

0
Comment
Question by:Omega002
  • 14
  • 8
  • 6
28 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16709802
select Datepart(month,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
from ticket_table
group by datepart(month,entered_date)
order by month(entered_date)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16709818
select Datepart(month,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
from ticket_table
where entered_date between '20060101' and getdate()
group by datepart(month,entered_date)
order by month(entered_date)
0
 

Author Comment

by:Omega002
ID: 16709967
How do I get the total for each month and how do I prompt for dates instead of hard coded dates?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16709995
Thia should do it.

select Datepart(month,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,
count(*) as Total
from ticket_table
where entered_date between '20060101' and getdate()
group by datepart(month,entered_date)
order by month(entered_date)
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16710021
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_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,
count(*) as Total
from ticket_table
where entered_date between @StartDate  and getdate()
group by datepart(month,entered_date)
order by month(entered_date)
0
 

Author Comment

by:Omega002
ID: 16710329
Ok so how can I display the months in a format other than 1 , 2, 3, etc..
0
 

Author Comment

by:Omega002
ID: 16710449
Also with the method you used to total each row is off and incorrect. Do you know how to use the rollup function?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16710451
sory use DATENAME instead of DATEPART

select DateNAME(month,entered_Date) as [ ],

...
group by datename(month,entered_date)


0
 

Author Comment

by:Omega002
ID: 16710626
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.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16710631
Is there another source code value than 1 - 6 for source_code ?

0
 

Author Comment

by:Omega002
ID: 16710786
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?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16710799
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)
0
 

Author Comment

by:Omega002
ID: 16710888
Ok everything works with the acception of the order by clause. It returns an error that say invalid order by clause.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16711009
Select datename(mm,convert(datetime,'2006'+right('0'+convert(varchar(2),mno),2)+'01') as [ ]
       ,Alarms,emails,letters,other,phone,web,total
  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
0
 

Author Comment

by:Omega002
ID: 16711167
Got an error. Incorrect syntax near the keyword 'as'. Line 5 incorrect syntax near 'sum'
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16711510
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
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16711515
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
0
 

Author Comment

by:Omega002
ID: 16711680
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.
0
 

Author Comment

by:Omega002
ID: 16711967
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
0
 

Author Comment

by:Omega002
ID: 16712196
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?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16712899
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)) Qry

I think this will work, but I don't have anything to test against.

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16712907
And yes, if you want to replace the '20060101'  witha variable, you'll want to declare it.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16715461
Select datename(mm,convert(datetime,'2006'+right('0'+convert(varchar(2),mno),2)+'01') as [ ]
       ,Alarms,emails,letters,other,phone,web,total
  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)

0
 

Author Comment

by:Omega002
ID: 16717827
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

0
 

Author Comment

by:Omega002
ID: 16717859
I get this error when I run this query:

Select datename(mm,convert(datetime,'2006'+right('0'+convert(varchar(2),mno),2)+'01') as [ ]
       ,Alarms,emails,letters,other,phone,web,total
  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'.
0
 

Author Comment

by:Omega002
ID: 16717882
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)) Qry

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.
0
 
LVL 13

Accepted Solution

by:
Atlanta_Mike earned 1000 total points
ID: 16718432
Dang... almost there...

SELECT Monthm, Alarms, Emails, Letters, Other, Phone, Web, Total
FROM (
select top 100 PERCENT 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)) Qry
0
 

Author Comment

by:Omega002
ID: 16720424
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
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question