Tough condition count and insertion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tough condition count and insertion

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-23 : 00:06:44

Hi everyone,

Really appreciate the solution for this scenario:
table1 has following cols:
Type------Code------Group------Date-------Early------Due-----Overdue
EP3-------110-------ABC--------2/11/11----0----------0-------0
VP5-------210-------XYZ--------11/2/11----0----------0-------0
... thousands of rows, Early, due and overdue are boolean flags.
Now I need to judge each row with condition1 of
If Type=EP3 or Code=210 then check if date is ahead of, on or past today's date
Else check next row.
Set one of the flags of early, due, overdue to 1 if gets true accordingly.
After that I need to count all the early, due, and overdue and write to table2 with cols like:
Condition--------Early--------Due------Overdue
Condit1----------121----------43-------76

Furtherly I need to judge each row by other conditions based on condition1. Like condition2 is condition1 plus Group='CBC' then compare the date with today's. Condition3 is condition1 plus Group='XYZ' ... Do the same as above and get count results to table2 as:
Condition--------Early--------Due------Overdue
Condit1----------121----------43-------76
Condit2----------98-----------67-------103
Condit3----------37-----------84-------119
...

Thanks in advance!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-23 : 00:27:57

[code]
SELECT Group,
COUNT(CASE WHEN Date>=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) THEN 1 ELSE NULL END) AS Early,
COUNT(CASE WHEN Date>=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND Date<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) THEN 1 ELSE NULL END) AS Due,
COUNT(CASE WHEN Date<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN 1 ELSE NULL END) AS Overdue
FROM table
WHERE (Type='EP3' or Code=210 )
GROUP BY Group
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-27 : 00:39:39

Thank you so much visakh16!
Now I want to run about 20 conditions in a stored procedure with above solution. How can I add the conditions as parameters? Say condition5 is group=bbb and condition19 is like group=ccc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 03:11:28

quote:
Originally posted by allan8964

Thank you so much visakh16!
Now I want to run about 20 conditions in a stored procedure with above solution. How can I add the conditions as parameters? Say condition5 is group=bbb and condition19 is like group=ccc.

Add all the conditions to WHERE clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources