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.
Author |
Topic |
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-15 : 12:19:09
|
I have a column of account numbers and a color assigned to them.most account numbers are all assigned a color, but some account numbers could have multiple colorsI need to get a list of the account numbers where there are multiple colors per account number.how would I Write that?select account number,colorfrom saleswhere color <> color this doesnt return anything |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-15 : 13:09:03
|
One of these, depending on your dataSELECT AccountNumberFROM SalesGROUP BY AccountNUmberHAVING COUNT(*) > 1-- or SELECT AccountNumberFROM SalesGROUP BY AccountNUmberHAVING COUNT(DISTINCT color) > 1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 14:18:28
|
if you want to select details also use thisSELECT *FROM(SELECT *,COUNT(Color) OVER (PARTITION BY AccountNumber) AS CntFROM Sales)tWHERE Cnt>1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-18 : 16:29:28
|
Thanks! those all work greatok the next part of it is sales datesI have a from date column and a to date column.Sometimes the dates could be like thisFrom TO1/1/12 1/1/121/1/12 1/1/121/2/12 1/3/12How would I count and total the number of distinct days?The answer using the data above would be 3 specific days, but how would I write the sql to total that per account number?Thanks for your help if you can! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-18 : 16:42:43
|
quote: Originally posted by montecarlo2079 Thanks! those all work greatok the next part of it is sales datesI have a from date column and a to date column.Sometimes the dates could be like thisFrom TO1/1/12 1/1/121/1/12 1/1/121/2/12 1/3/12How would I count and total the number of distinct days?The answer using the data above would be 3 specific days, but how would I write the sql to total that per account number?Thanks for your help if you can!
It would be a little simpler if you didn't have those duplicate rows. If you have duplicate rows, then do it like this:Create a calendar table first, if you don't have one already:CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20120101' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20121231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0); Now you can query like shown belowSELECT accountNumber, COUNT(DISTINCT Dt) AS DaysFROM Sales s INNER JOIN #Calendar c ON s.[From] <= b.Dt AND a.[TO] >= b.DtGROUP BY accountNumber; |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-20 : 15:49:17
|
it works to a point.here is an example of the dataICN from date of service TO date of service status 12345 20121203 12:00:00 20121203 12:00:00 P12345 20121203 12:00:00 20121203 12:00:00 P12345 20121203 12:00:00 20121203 12:00:00 P12345 20121203 12:00:00 20121203 12:00:00 P12345 20121203 12:00:00 20121203 12:00:00 P12345 20121206 12:00:00 20121206 12:00:00 P12345 20121206 12:00:00 20121206 12:00:00 P12345 20121206 12:00:00 20121206 12:00:00 P12345 20121206 12:00:00 20121206 12:00:00 P12345 20121206 12:00:00 20121206 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121210 12:00:00 20121210 12:00:00 P12345 20121218 12:00:00 20121218 12:00:00 P12345 20121218 12:00:00 20121218 12:00:00 P12345 20121218 12:00:00 20121218 12:00:00 P12345 20121218 12:00:00 20121218 12:00:00 P12345 20121218 12:00:00 20121218 12:00:00 P12345 20121231 12:00:00 20121231 12:00:00 P12345 20121231 12:00:00 20121231 12:00:00 P12345 20121231 12:00:00 20121231 12:00:00 P12345 20121231 12:00:00 20121231 12:00:00 P12345 20121231 12:00:00 20121231 12:00:00 PThe count should be 5, but the sql returns 28 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 16:58:13
|
Not sure why you got 28. I made the assumption that the dates wouldn't have a time portion to it. In any case, I copied your data to create DDL for the tables and data and the result is indeed 5. Had to make a few changes to use the new column names and to account for the dates having a time portion.CREATE TABLE #Sales(ICN INT, fromDate DATETIME, toDate DATETIME, serviceStatus CHAR(1));INSERT INTO #Sales VALUES('12345','20121203 12:00:00','20121203 12:00:00','P'),('12345','20121203 12:00:00','20121203 12:00:00','P'),('12345','20121203 12:00:00','20121203 12:00:00','P'),('12345','20121203 12:00:00','20121203 12:00:00','P'),('12345','20121203 12:00:00','20121203 12:00:00','P'),('12345','20121206 12:00:00','20121206 12:00:00','P'),('12345','20121206 12:00:00','20121206 12:00:00','P'),('12345','20121206 12:00:00','20121206 12:00:00','P'),('12345','20121206 12:00:00','20121206 12:00:00','P'),('12345','20121206 12:00:00','20121206 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121210 12:00:00','20121210 12:00:00','P'),('12345','20121218 12:00:00','20121218 12:00:00','P'),('12345','20121218 12:00:00','20121218 12:00:00','P'),('12345','20121218 12:00:00','20121218 12:00:00','P'),('12345','20121218 12:00:00','20121218 12:00:00','P'),('12345','20121218 12:00:00','20121218 12:00:00','P'),('12345','20121231 12:00:00','20121231 12:00:00','P'),('12345','20121231 12:00:00','20121231 12:00:00','P'),('12345','20121231 12:00:00','20121231 12:00:00','P'),('12345','20121231 12:00:00','20121231 12:00:00','P'),('12345','20121231 12:00:00','20121231 12:00:00','P');CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20121201' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20121231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);SELECT ICN, COUNT(DISTINCT Dt) AS DaysFROM #Sales s INNER JOIN #Calendar c ON s.fromDate >= c.Dt AND s.toDate < c.Dt+1GROUP BY ICN; |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 09:08:50
|
Thank you for taking the time to help me with this.I Think this would work, but I have a question.I have about 2 million rows with multiple ICNs and multiple dates per ICNhow would I insert all of these values into a temp table as you are doing.would I need to write a cursor?Basically what im trying to accomplish is that I have a table of medical claims. about 2 million rows total. I have the ICN, from date of service, to date of service, and P for Paid status.I need to figure out and count the specific number of distinct days per ICN. so I dont need to take the smallest DOS and the largest date of service to calculate. I need to be able to go through each ICN, and count the distinct days, EVEN if there are overlapping dates on one row that overlaps dates on another row.example12345 20121203 12:00:00 20121205 12:00:00 P12345 20121204 12:00:00 20121204 12:00:00 P12345 20121203 12:00:00 20121206 12:00:00 PTechnically the total number of distinct dates of service should be 4.How could I write something, or mock up the sql you gave me to accomplish that? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 09:16:37
|
I created the #Sales temp table only to test the query. If your actual Sales table has the same schema, you would use the same query, but would replace #Sales with Sales (or whatever is your actual table name).Before you do it on a production system, try it out in a test environment to make sure it is doing what you expect it to do.quote: I need to figure out and count the specific number of distinct days per ICN.
That is what the query should do.quote: so I dont need to take the smallest DOS and the largest date of service to calculate.
I didn't follow what you meant by this. quote: I need to be able to go through each ICN, and count the distinct days, EVEN if there are overlapping dates on one row that overlaps dates on another row.
The query should do this correctly.When you test, use a sample data set that has the various scenarios such as overlapping dates, multiple ICN's etc. and verify that they are all working correctly. If you find some examples where it is not working correctly, post that set of sample data. |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 09:34:05
|
ok lets start from the beginning.I have my table of 2 million rows. For each ICN, I need to count the distinct number of days that could appear in the FROM DATE OF SERVICE or TO DATE OF SERVICE fields. Even if days overlap, I still only need to count them once.Each row in the table has 4 values;ICN, FROM DATE OF SERVICE, TO DATE OF SERVICE, PAID STATUS.Each ICN could have 1 line, or it could have 60 lines. but for each ICN, I need to count the distinct number days that appear in all rows for that ICN.so it could be 1 row that looks like thisICN From Date of Service To Date of Service Paid Status 12345 1/1/13 1/1/13 PTHe result of this line should be 1 day for this icn.I could also have varying rows be icnexample:ICN FROM DATE OF SERVICE TO DATE OF SERVICE PAID STATUS12345 1/1/2013 1/5/2013 P12345 1/8/2013 1/10/2013 p12345 1/2/2013 1/7/2013 p12345 1/9/2013 1/15/2013 p12345 1/8/2013 1/15/2013 pThe total days in this icn would be 15. but as you can see, there are overlapping days, and a mixture of dates in the from and to dates of service.how do I incorporate what you have already given me, in order to go through and give me the correct answer going through 2 million rows? |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 09:52:25
|
I actually think your query might have done it. im spot checking items now and so far it looks good. |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 10:26:50
|
James,The only problem I found so far is that I have a list of about 29 ICNs that did not show up in the query. is there something in the inner join that is causing them to drop off?HEre is what I have so farCREATE TABLE #Calendar2(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20120101' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20131231')INSERT INTO #Calendar2 SELECT c FROM cte OPTION (MAXRECURSION 0);SELECT ICN, COUNT(DISTINCT Dt) AS DaysFROM AllOPBYDOS s INNER JOIN #Calendar2 c ON s.[from Date of service] >= c.Dt AND s.[to Date of service] < c.Dt+1Where [Detail Status Code] = 'p'GROUP BY ICN; |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 10:37:31
|
Pick one of the missing ICN's and print out the rows from the AllOPBYDOS table. That should tell us why they are not being picked up. Post that data if you can. |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 10:43:21
|
ICN From Date of Service To Date of Service Detail Status Code12345 20121102 12:00:00 20121103 12:00:00 P12345 20121102 12:00:00 20121103 12:00:00 P12345 20121102 12:00:00 20121103 12:00:00 P12345 20121102 12:00:00 20121103 12:00:00 D12345 20121102 12:00:00 20121103 12:00:00 D12345 20121102 12:00:00 20121103 12:00:00 D12345 20121102 12:00:00 20121103 12:00:00 D12345 20121102 12:00:00 20121103 12:00:00 D |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 11:32:03
|
just some more information.all The dates Im dealing with go from 7/1/2012 to 6/30/2013(running total so I can run this each week). Im not sure if that has something to do with it?I just dont see why these specific ICN's would have dropped off. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 12:35:59
|
This has to do with our date logic - something must be incorrect. I want to run your example and will post back again. |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-21 : 12:37:39
|
thanks james!when I run this.SELECT ICN, COUNT(DISTINCT Dt) AS Days, sum(case when [from Date of service] not between const.mindt and const.maxdt or [to Date of service] not between const.mindt and const.maxdt then 1 else 0 end) as OutOfRangeRecordsFROM AllOPBYDOS s left outer JOIN #Calendar2 c ON s.[from Date of service] >= c.Dt AND s.[to Date of service] < c.Dt+1 cross join (select MAX(dt) as maxdt, MIN(dt) as mindt from #Calendar2) const where [Detail Status Code] = 'p'GROUP BY ICNorder by DaysI get 29 records that have a value of 0 in the column 'days' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 16:35:22
|
Going back to the case you posted on 02/21/2013 : 10:43:21, can you see if this gives you the correct answers? If it does, can you adapt this to use your tables?CREATE TABLE #Sales(ICN INT, fromDate DATETIME, toDate DATETIME, serviceStatus CHAR(1));INSERT INTO #Sales VALUES('12345','20121102 12:00:00','20121103 12:00:00','P'),('12345','20121102 12:00:00','20121103 12:00:00','P'),('12345','20121102 12:00:00','20121103 12:00:00','P'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D');CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20121101' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20121231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);SELECT ICN, COUNT(DISTINCT Dt) AS DaysFROM #Sales s INNER JOIN #Calendar c ON CAST( s.fromDate AS DATE) <= c.Dt AND CAST(s.toDate AS DATE) >= c.DtGROUP BY ICN; DROP TABLE #Sales, #Calendar; |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-22 : 08:56:03
|
James, that worked. it gave me 2 as the days.now the question isCREATE TABLE #Sales(ICN INT, [from date of service] DATETIME, [to Date of service]DATETIME, [detail status code] CHAR(1));INSERT INTO #Sales VALUES('12345','20121102 12:00:00','20121103 12:00:00','P'),('12345','20121102 12:00:00','20121103 12:00:00','P'),('12345','20121102 12:00:00','20121103 12:00:00','P'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D'),('12345','20121102 12:00:00','20121103 12:00:00','D');do I need this part of the query to insert data into?or can I just run CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20121101' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20121231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);SELECT ICN, COUNT(DISTINCT Dt) AS DaysFROM AllOPBYDOS s INNER JOIN #Calendar c ON CAST( s.[from date of service]AS DATE) <= c.Dt AND CAST(s.[to Date of service] AS DATE) >= c.DtGROUP BY ICN; DROP TABLE #Sales, #Calendar; |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-22 : 09:09:14
|
You don't need the part where we create the #Sales table and insert the data. That was just for testing. But you do need the #Calendar table. Be sure to insert the range of dates that are of interest to you into that table. For example, for all of 2012, see in red in the query below.CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS(SELECT CAST('20120101' AS DATETIME) AS c UNION ALLSELECT DATEADD(dd,1,c) FROM cte WHERE c < '20121231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0); Also, I saw in one of your earlier postings that you wanted to check for [detail status code] = 'P' If you need that, include that as well in the query. See in red below:SELECTICN,COUNT(DISTINCT Dt) AS DaysFROMAllOPBYDOS sINNER JOIN #Calendar c ONCAST( s.[from date of service]AS DATE) <= c.Dt AND CAST(s.[to Date of service] AS DATE) >= c.Dt AND s.[detail status code] = 'P'GROUP BYICN; |
|
|
montecarlo2079
Starting Member
20 Posts |
Posted - 2013-02-22 : 09:23:45
|
James,I think this worked. I had to change the dates, but this returned all of my results.CREATE TABLE #Sales(ICN float, fromDate DATETIME, toDate DATETIME, serviceStatus CHAR(1));INSERT INTO #Sales select icn,[from date of service],[to date of service], [detail status code]from AllOPBYDOSCREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);;WITH cte AS( SELECT CAST('20120101' AS DATETIME) AS c UNION ALL SELECT DATEADD(dd,1,c) FROM cte WHERE c < '20131231')INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);SELECT ICN, COUNT(DISTINCT Dt) AS DaysFROM #Sales s INNER JOIN #Calendar c ON CAST( s.fromDate AS DATE) <= c.Dt AND CAST(s.toDate AS DATE) >= c.DtGROUP BY ICN; DROP TABLE #Sales, #Calendar; |
|
|
Next Page
|
|
|
|
|