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
 Help with query

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 colors

I 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,
color
from sales
where 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 data
SELECT AccountNumber
FROM Sales
GROUP BY AccountNUmber
HAVING COUNT(*) > 1

-- or

SELECT AccountNumber
FROM Sales
GROUP BY AccountNUmber
HAVING COUNT(DISTINCT color) > 1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 14:18:28
if you want to select details also use this

SELECT *
FROM
(
SELECT *,COUNT(Color) OVER (PARTITION BY AccountNumber) AS Cnt
FROM Sales
)t
WHERE Cnt>1


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

Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-02-18 : 16:29:28
Thanks! those all work great

ok the next part of it is sales dates
I have a from date column and a to date column.

Sometimes the dates could be like this

From TO
1/1/12 1/1/12
1/1/12 1/1/12
1/2/12 1/3/12


How 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!

Go to Top of Page

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 great

ok the next part of it is sales dates
I have a from date column and a to date column.

Sometimes the dates could be like this

From TO
1/1/12 1/1/12
1/1/12 1/1/12
1/2/12 1/3/12


How 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 below
SELECT
accountNumber,
COUNT(DISTINCT Dt) AS Days
FROM
Sales s
INNER JOIN #Calendar c ON
s.[From] <= b.Dt AND a.[TO] >= b.Dt
GROUP BY
accountNumber;
Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-02-20 : 15:49:17
it works to a point.

here is an example of the data

ICN from date of service TO date of service status
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


The count should be 5, but the sql returns 28
Go to Top of Page

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 Days
FROM
#Sales s
INNER JOIN #Calendar c ON
s.fromDate >= c.Dt AND s.toDate < c.Dt+1
GROUP BY
ICN;
Go to Top of Page

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 ICN

how 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.

example


12345 20121203 12:00:00 20121205 12:00:00 P
12345 20121204 12:00:00 20121204 12:00:00 P
12345 20121203 12:00:00 20121206 12:00:00 P


Technically 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?
Go to Top of Page

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.
Go to Top of Page

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 this

ICN From Date of Service To Date of Service Paid Status
12345 1/1/13 1/1/13 P

THe result of this line should be 1 day for this icn.


I could also have varying rows be icn
example:

ICN FROM DATE OF SERVICE TO DATE OF SERVICE PAID STATUS
12345 1/1/2013 1/5/2013 P
12345 1/8/2013 1/10/2013 p
12345 1/2/2013 1/7/2013 p
12345 1/9/2013 1/15/2013 p
12345 1/8/2013 1/15/2013 p

The 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?


Go to Top of Page

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.

Go to Top of Page

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 far

CREATE 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 Days
FROM
AllOPBYDOS s
INNER JOIN #Calendar2 c ON
s.[from Date of service] >= c.Dt AND s.[to Date of service] < c.Dt+1
Where [Detail Status Code] = 'p'
GROUP BY
ICN;
Go to Top of Page

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.
Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-02-21 : 10:43:21
ICN From Date of Service To Date of Service Detail Status Code
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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 OutOfRangeRecords
FROM 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 ICN
order by Days


I get 29 records that have a value of 0 in the column 'days'
Go to Top of Page

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 Days
FROM
#Sales s
INNER JOIN #Calendar c ON
CAST( s.fromDate AS DATE) <= c.Dt AND CAST(s.toDate AS DATE) >= c.Dt
GROUP BY
ICN;

DROP TABLE #Sales, #Calendar;
Go to Top of Page

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 is

CREATE 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 Days
FROM
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.Dt
GROUP BY
ICN;

DROP TABLE #Sales, #Calendar;
Go to Top of Page

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 ALL
SELECT 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:
SELECT
ICN,
COUNT(DISTINCT Dt) AS Days
FROM
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.Dt
AND s.[detail status code] = 'P'
GROUP BY
ICN;
Go to Top of Page

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 AllOPBYDOS



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 < '20131231'
)
INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);


SELECT
ICN,
COUNT(DISTINCT Dt) AS Days
FROM
#Sales s
INNER JOIN #Calendar c ON
CAST( s.fromDate AS DATE) <= c.Dt AND CAST(s.toDate AS DATE) >= c.Dt
GROUP BY
ICN;

DROP TABLE #Sales, #Calendar;
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -