Count only unique dates from a subquery

Question

Post reply

Count only unique dates from a subquery

JeremyU

SSChasing Mays

Points: 640
More actions
October 14, 2023 at 4:34 am

#4307819
Hello,
I have a question. I have a code where I am trying to calculate 'Count of Days with Sales'. This part of SQL looks like the code I provide below.
It roughly means that if POADAT (Activity Date) is within - 7 days of Week Ending Date and actual Week Ending date then count 1 if not then 0.
This works for most items, however this table POSDLYCHMQ sometimes has repetitions of the same dates and SQL for some reason counts them as separate dates. When I typed 'DISTINCT' nothing has happened.
Does anyone know how else we may make them UNIQUE so SQL counts only distinct values, maybe any other ideas. Thanks.
```
SELECT *, 
SUM(CASE WHEN cast(cast(sq.P0ADAT as varchar) as date) 
BETWEEN DATEADD(DAY,-7,DATEDIFF(DAY,0,cast(cast(sq.P0WEDT as varchar) as date))) AND cast(cast(sq.P0WEDT as varchar) as date)
    THEN 1 ELSE 0 END) AS 'Count # of Days with Sales'

FROM POSDLYCHMQ
LEFT OUTER JOIN (SELECT DISTINCT  P0ISBN, p0CHN, P0WEDT, P0ADAT 
FROM POSDLYCHMQ

GROUP BY P0ISBN, p0CHN, P0WEDT, P0ADAT) SQ 
on p.P0ISBN=sq.P0ISBN and p. p0CHN=sq.p0CHN 
and p.P0WEDT=sq.P0WEDT and 
p.P0ADAT=sq.P0ADAT
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Your DISTINCT, as written, applies to the entire combination of (P0ISBN, p0CHN, P0WEDT, P0ADAT), not only to the date.

If you provide your data in a consumable format, someone here will help with the necessary adjustments.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

JeremyU wrote:

Hello,
I have a question.

Me too. Where's the "Readily Consumable" test data and the desired results that match the test data?

With that being said, be advised... You've been asked to do so many times and, so, this will be the last time I respond to one of your posts that doesn't contain properly working code for "Readily Consumable" test data. If you don't want to help us help you with YOUR problem, so be it.

Here's one final example of how to post "Readily Consumable" test data (obviously, for the problem you posted). Had you posted something like this, you'd already have a coded answer: 😉

/******************************************************************************
 Create "Readily Consumable" test data.
 This is not a part of the solution. We're just creating test data here.
******************************************************************************/--===== If the test table exists, drop it to make reruns in SSMS easier.
     -- And, yes... Although there are some rare exceptions, it should be a 
     -- a Temp Table so people don't have to clean up their database once 
     -- they're done helping you with YOUR problem.
   DROP TABLE IF EXISTS #POSDLYCHMQ;
GO
--===== Create the test table.
     -- This eliminates all questions about datatypes/NULL ability, etc.
     -- If you have keys or indexes, this is where to add them.
 CREATE TABLE #POSDLYCHMQ
        (
         P0ISBN VARCHAR(10) NOT NULL
        ,p0CHN  VARCHAR(2)  NOT NULL
        ,P0WEDT INT         NOT NULL
        ,P0ADAT INT         NOT NULL
        )
;
GO
--===== Populate the test table with example data.
 INSERT INTO #POSDLYCHMQ WITH (TABLOCK)
        ( P0ISBN,   p0CHN,   P0WEDT,   P0ADAT)
 SELECT v.P0ISBN, v.p0CHN, v.P0WEDT, v.P0ADAT
   FROM (VALUES
         ('0077833426','MJ',20230930,20230924)
        ,('0077833426','MJ',20230930,20230925)
        ,('0077833426','MJ',20230930,20230926)
        ,('0077833426','MJ',20230930,20230927)
        ,('0077833426','MJ',20230930,20230928)
        ,('0077833426','MJ',20230930,20230929)
        ,('0077833426','MJ',20230930,20230910)
        ,('0077833426','MJ',20230930,20230924)
        ,('0077833426','MJ',20230930,20230925)
        ,('0077833426','MJ',20230930,20230926)
        ,('0077833426','MJ',20230930,20230927)
        ,('0077833426','MJ',20230930,20230928)
        ,('0077833426','MJ',20230930,20230929)
        ,('0077833426','MJ',20230930,20230910)
        )v(P0ISBN, p0CHN, P0WEDT, P0ADAT)
;
GO
--===== This ensures that the test data code works.
     -- You need to run this whole bit because
     -- code that doesn't work is useless to the 
     -- people that can help you with your problem.
 SELECT *
   FROM #POSDLYCHMQ
;
GO

Here's one possible solution but I don't know for sure because you also didn't post the "Desired Results" for the given test data.

--===== Without an example of the desired output,
     -- here's my guess as to what you want.
     -- If it's wrong, it's your fault. ;)
 SELECT  P0ISBN, p0CHN, P0WEDT, P0ADAT
        ,DailySalesCount = COUNT(*)
   FROM #POSDLYCHMQ
  GROUP BY P0ISBN, p0CHN, P0WEDT, P0ADAT
;

We didn't have to check for the "within 7 days" thing because analysis of the data tells me that the dates in the given data will ALWAYS observe the proper 7 day relationship between the P0WEDT and P0ADAT columns. That type of "data validation/cleansing" to make sure of such a thing should be done in separate code long before this "reporting code" is used because such an issue should NOT be simply "covered up" by some reporting code.

If you want the INT dates to be converted to actual DATE datatypes for output of the code above, I'll leave that to you because you have to have some of the "fun" and I don't actually know what "display format" you want or even if one is needed because you didn't post the desired result. 😉 As a bit of a sidebar there, I strongly recommend the use of DATE_FROM_PARTS along with a little Integer Math to go along with splitting the INT dates to a valid Year, Month, and Day of Month value for use with that function. The reason for that is that character-based conversions tend to be slower than some good ol' Integer Math. You can do the performance tests for this particular case on your own.

Like I said, I'm just going to ignore any of your future posts that don't contain the stuff we need to properly answer your questions. Based on your current track record of ignoring such advice, it was nice knowing you. 😉 Good luck.

p.s. If all that sounded a bit mean, that's good. It means that you actually read what I wrote this time. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101121 More actions · Answer 3

Maybe this, if I understand the requirements correctly:

SELECT *
FROM POSDLYCHMQ P
LEFT OUTER JOIN (SELECT P0ISBN, p0CHN, P0WEDT, 
COUNT(DISTINCT CASE WHEN cast(cast(sq.P0ADAT as varchar(30)) as date) 
BETWEEN DATEADD(DAY,-7,DATEDIFF(DAY,0,cast(cast(sq.P0WEDT as varchar(30)) as date))) AND cast(cast(sq.P0WEDT as varchar(30)) as date)
    THEN cast(cast(sq.P0ADAT as varchar(30)) as date) ELSE NULL END) AS 'Count # of Days with Sales'
FROM POSDLYCHMQ
GROUP BY P0ISBN, p0CHN, P0WEDT) SQ 
on p.P0ISBN=sq.P0ISBN and p. p0CHN=sq.p0CHN 
and p.P0WEDT=sq.P0WEDT /*and
p.P0ADAT=sq."Count # of Days with Sales"*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".