2 SQL combined

[lhg1]

Hi

I have 2 SQL's that gives me how
- many total pr week
- overdye pr week.

I'd like it in one SQL and not having to excel the result

This is total.

SELECT 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) As year,
DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) as week,
count(*) as amount
FROM SC_Helpdesk 
where ( Assigned_Group  = 'IS IT - PF Forvaltning Billing' or Assigned_Group  = 'Billing Drift' or Assigned_Group  = 'Nummeradministration')
and ticket_type=7  -- Only INCIDENTS
AND (source <> 4 OR source = NULL)  -- No Alarms
--and DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) = '12'
and YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) = '2010'
and priority < 2
GROUP BY 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))
order by 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))

Giving something like this.
year week amount
2010 1 2
2010 2 23
2010 3 19
2010 4 8

The other with overdue.

SELECT 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) As year,
DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) as week,
count(*) as Antal
FROM SC_Helpdesk 
where ( Assigned_Group  = 'IS IT - PF Forvaltning Billing' or Assigned_Group  = 'Billing Drift' or Assigned_Group  = 'Nummeradministration')
and ticket_type=7  -- KUN INCIDENTS
--AND (source <> 4 OR source = NULL)  -- TAGER ALARMER FRA
--and DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) = '12'
and YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) = '2010'
and priority < 2
and 
( --CRITICAL
((priority = 0 and status > 3 and CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120) < 
  dateadd(hh,-24,CONVERT(VARCHAR, DATEADD(ss, resolved_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))) 
or
 (priority = 0 and status < 4 and CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120) < 
  dateadd(hh,-24,getdate())) )
or -- HIGH
((priority = 1 and status > 3 and CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120) < 
  dateadd(hh,-120,CONVERT(VARCHAR, DATEADD(ss, resolved_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))) 
or
 (priority = 1 and status < 4 and CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120) < 
  dateadd(hh,-120,getdate())) )
)
GROUP BY 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))
order by 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(wk,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))

Also the same output format.
year week Antal
2010 1 1
2010 2 7
2010 3 7
2010 4 1

What I'm trying to do is make it into one, and also do a procent not overdue - witch I do in excel now.

So the end result is something like this.

year week Total Overdue Pct. not overdue
2010 1 50 2 96%
2010 2 40 4 90%
2010 3 60 5 92%
2010 4 100 3 97%

The big thing for me is putting the 2 SQL's togetter, altså the overdue can have "missing" rows, if. ex there are none for week 2

Thanks
LHG

 

[gmmastros]

What version of SQL Server are you using? SQL 2000, 2005, 2008, 2008 R2?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If you are using SQL2005 or newer, you can combine your queries easily by using common table expressions. The basic syntax would be this...

; With 
Total as 
  ([green]
  -- Your first query here.
  -- Make sure you remove the order by.[/green]
  ), 
OverDue As
  ([green]
  -- Your second query here
  -- Make sure you remove the order by[/green]
  )

Select  Total.Year, 
        Total.Week, 
        Total.Amount, 
        Overdue.Antal
From    Total
        Left Join Overdue
          On  Total.Year = Overdue.Year
          And Total.Week = Overdue.Week
Order By Total.Year, Total.Week

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think there are several ways you can make this query better. Since my suggestions don't relate directly to the original question, I'll ask... Would you like some suggestions regarding other aspects of this query?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

in this situation, it would probably be better to perform a self join since both queries only operate on the same table, rather than using cte's...

--------------------
Procrastinate Now!

 

[gmmastros]

Actually, I was looking at this a little closer and I think it can be accomplished without any joins because the 2 queries are actually quite similar. A "Count(complicated case statement)" should work here. No joins, not cte's, and it's even possible to make this monster sargable which would likely improve performance (assume an index on create_date exists).

[small]P.S. Note to self.... stop trying to suggest cte's.[/small]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom