Author |
Topic |
avitron2020
Starting Member
3 Posts |
Posted - 2013-05-08 : 15:11:51
|
I'm looking for a SQL statement that will output 'new vs duplicate' data on a monthly bases, but I can't seem to come up SQL statement that can accomplish what I need. See example below to get a better idea of what I'm trying to do since this is hard to explain.Example data:id, num, create_date// January1, 34250, 2012-01-012, 25383, 2012-01-033, 93482, 2012-01-134, 52934, 2012-01-205, 34823, 2012-01-23// Febuary6, 09943, 2012-02-037, 32845, 2012-02-058, 34250, 2012-02-16 -- same num as id 19, 57325, 2012-02-2810, 34823, 2012-02-28 -- same num as id 5// March11, 95234, 2012-03-0512, 25383, 2012-03-03 -- same num as id 213, 32845, 2012-03-16 -- same num as id 714, 34250, 2012-03-28 -- same num as id 1, 815, 12412, 2012-03-28// April16, 53535, 2012-04-0517, 23232, 2012-04-0318, 57325, 2012-04-16 -- same num as id 919, 34250, 2012-04-28 -- same num as id 1, 8, 1420, 12343, 2012-04-28Example results from data above would be something like below (just looking for new vs duplicate numbers):January: 5 new and 0 duplicates (no duplicates will ever be found the first month)Febuary: 3 new and 2 duplicates (checks for duplicates in the month of Jan only)March: 2 new and 3 duplicates (checks for duplicates in the month of Jan & Feb only)April: 3 new and 2 duplicates (checks for duplicates in the month of Jan, Feb & March only)...and so on for each month after thatHopefully you can see what I'm trying to do. Also, the total amount (new + duplicate) will always be 5 based on the data above. Any real smart person out there that can help?Note: I'm using SQL Server 2005 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-08 : 16:30:28
|
Give this a trySELECT YEAR(create_date), MONTH(create_date), COUNT(*) - COUNT(DISTINCT b.dups) AS New, COUNT(DISTINCT b.dups) AS DupsFROM YourTable a OUTER APPLY ( SELECT num FROM YourTable b WHERE b.create_date < DATEADD(mm,DATEDIFF(mm,0,a.create_date),0) --AND b.create_date >= DATEADD(yy,DATEDIFF(yy,0,a.create_date),0) AND a.num = b.num ) b |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-09 : 00:51:44
|
[code]SELECT YEAR(create_date) AS [Year],MONTH(create_date) AS [Month],SUM(CASE WHEN Rn=1 THEN 1 ELSE 0 END) AS New,SUM(CASE WHEN Rn > 1 THEN 1 ELSE 0 END) AS DuplicateFROM(SELECT ROW_NUMBER() OVER (PARTITION BY num ORDER BY create_Date) AS Rn,*FROM Table)tGROUP BY YEAR(create_date),MONTH(create_date)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-09 : 01:03:30
|
quote: Originally posted by James K Give this a trySELECT YEAR(create_date), MONTH(create_date), COUNT(*) - COUNT(DISTINCTSUM(CASE WHEN b.dups>0 THEN 1 ELSE 0 END) AS New, COUNT(DISTINCT b.dups)SUM(CASE WHEN COALESCE(b.dups,0)=0 THEN 1 ELSE 0 END) AS DupsFROM YourTable a OUTER APPLY ( SELECT numCOUNT(*) AS dups FROM YourTable b WHERE b.create_date < DATEADD(mm,DATEDIFF(mm,0,a.create_date),0) --AND b.create_date >= DATEADD(yy,DATEDIFF(yy,0,a.create_date),0) AND a.num = b.num ) bGROUP BY YEAR(create_date), MONTH(create_date)
Some tweaks to get expected result------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
avitron2020
Starting Member
3 Posts |
Posted - 2013-05-09 : 10:39:33
|
Thanks. I'll try it out today and let you know how it works. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-10 : 00:17:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|