May 20, 2020 at 3:47 am
I have this table:
CREATE TABLE dbo.legalchanges (
userid bigint NOT NULL,
TCChanges int NOT NULL,
NonCompChanges int NOT NULL,
ActChanges int NOT NULL,
CatChanges int NOT NULL,
SubCatChanges int NOT NULL,
ExFromCompChanges int NOT NULL,
MatterChanges int NOT NULL,
BlockChanges int NOT NULL,
VagueChanges int NOT NULL,
datecreated varchar(27) NOT NULL
);
The data looks like this:
INSERT INTO dbo.legalchanges (userid, TCChanges, NonCompChanges, ActChanges, CatChanges, SubCatChanges, ExFromCompChanges, MatterChanges, BlockChanges, VagueChanges, datecreated) VALUES
(4, 1, 3, 2, 4, 3, 16, 0, 0, 5, '1/1/2020'),
(4, 1, 15, 1, 1, 1, 9, 0, 1, 7, '12/1/2019'),
(4, 1, 134, 0, 0, 0, 2, 0, 0, 89, '2/1/2020'),
(4, 2, 26, 2, 2, 0, 2, 0, 10, 0, '3/1/2020'),
(11, 0, 1, 0, 0, 0, 0, 0, 0, 1, '1/1/2020'),
(11, 0, 1, 0, 0, 0, 0, 0, 0, 1, '2/1/2020'),
(11, 0, 46, 6, 12, 12, 0, 0, 0, 40, '3/1/2020'),
(11, 0, 1, 0, 0, 0, 0, 0, 0, 1, '4/1/2020'),
(11, 0, 9, 0, 0, 0, 0, 0, 0, 0, '5/1/2020'),
(13, 1, 4, 0, 0, 0, 0, 0, 0, 2, '1/1/2020'),
(13, 0, 9, 0, 0, 0, 6, 0, 1, 2, '12/1/2019'),
(13, 9, 104, 8, 4, 125, 18, 0, 3, 26, '2/1/2020'),
(13, 91, 155, 79, 86, 81, 60, 0, 11, 66, '3/1/2020'),
(13, 4, 128, 1, 4, 3, 0, 0, 0, 3, '4/1/2020'),
(13, 0, 67, 10, 20, 9, 0, 0, 0, 0, '5/1/2020'),
(14, 0, 4, 0, 0, 0, 0, 0, 0, 1, '1/1/2020'),
(14, 0, 2, 0, 0, 0, 0, 0, 1, 1, '12/1/2019'),
(14, 0, 88, 2, 4, 8, 4, 0, 0, 12, '2/1/2020'),
(14, 38, 157, 30, 44, 27, 29, 0, 85, 68, '3/1/2020'),
(14, 0, 55, 0, 0, 0, 0, 0, 0, 7, '4/1/2020'),
(14, 1, 3, 0, 0, 0, 0, 0, 0, 0, '5/1/2020'),
(18, 2, 0, 1, 6, 3, 7, 0, 0, 0, '1/1/2020'),
(18, 0, 4, 0, 0, 0, 0, 0, 0, 4, '12/1/2019'),
(18, 6, 2, 0, 0, 22, 7, 0, 0, 2, '2/1/2020'),
(18, 43, 301, 101, 129, 124, 9, 0, 0, 211, '3/1/2020'),
(18, 865, 41, 330, 276, 168, 4, 0, 0, 0, '4/1/2020'),
(18, 0, 39, 0, 0, 0, 0, 0, 0, 0, '5/1/2020'),
(19, 11, 26, 4, 3, 1, 2, 0, 0, 6, '1/1/2020'),
(19, 0, 7, 0, 0, 0, 1, 0, 1, 4, '12/1/2019'),
(19, 0, 104, 0, 0, 7, 4, 0, 7, 50, '2/1/2020'),
(19, 10, 42, 5, 6, 6, 14, 0, 1, 6, '3/1/2020'),
(19, 5, 60, 9, 13, 4, 0, 0, 3, 40, '4/1/2020'),
(19, 0, 14, 0, 0, 0, 0, 0, 0, 2, '5/1/2020'),
(20, 2, 2, 2, 2, 0, 0, 0, 0, 2, '1/1/2020'),
(20, 0, 14, 0, 0, 0, 1, 0, 2, 9, '12/1/2019'),
(20, 18, 34, 12, 14, 13, 16, 0, 0, 17, '2/1/2020'),
(20, 112, 341, 64, 74, 44, 42, 0, 11, 104, '3/1/2020'),
(20, 9, 66, 7, 9, 6, 15, 0, 8, 10, '4/1/2020'),
(20, 1, 109, 0, 0, 0, 0, 0, 0, 0, '5/1/2020');
I need each user to have records for each date. So, for example, you see that userid 4 doesn't have records for 4/1/2020 or 5/1/2020; however, I need that user to have 0 for each field for 4/1/2020 and 5/1/2020, when I query the table. This table will continue to grow as the year grows, so I need the query to automatically have records for all dates in the table if that's possible?
Thank you for any help you can provide.
Jordon
May 20, 2020 at 5:55 am
This should do the trick for you. It hasn't populated the original table with the extra rows but it creates the source we could with. I'm just not sure that's necessary. Why do you want to do with the rows for the non-transaction days?
Also, the code will be self maintaining up 'til 12/31/9999.
WITH cteDateRange AS
(
SELECT MinDate = MIN(CONVERT(DATETIME,datecreated,110))
,MaxDate = MAX(CONVERT(DATETIME,datecreated,110))
FROM dbo.legalchanges
)
,cteDateDomain AS
(
SELECT datecreated = DATEADD(dd,t.N,dr.MinDate)
FROM cteDateRange dr
CROSS APPLY dbo.fnTally(0,DATEDIFF(dd,dr.MinDate,dr.MaxDate)) t
)
,cteKeyUp AS
(
SELECT lc.userid
,dt.datecreated
FROM cteDateDomain dt
CROSS JOIN dbo.legalchanges lc
GROUP BY dt.datecreated,lc.userid
)
SELECT ku.userid
,TCChanges = ISNULL(lc.TCChanges ,0)
,NonCompChanges = ISNULL(lc.NonCompChanges ,0)
,ActChanges = ISNULL(lc.ActChanges ,0)
,CatChanges = ISNULL(lc.CatChanges ,0)
,SubCatChanges = ISNULL(lc.SubCatChanges ,0)
,ExFromCompChanges = ISNULL(lc.ExFromCompChanges,0)
,MatterChanges = ISNULL(lc.MatterChanges ,0)
,BlockChanges = ISNULL(lc.BlockChanges ,0)
,VagueChanges = ISNULL(lc.VagueChanges ,0)
,ku.datecreated
FROM cteKeyUp ku
LEFT JOIN dbo.legalchanges lc
ON ku.userid = lc.userid
AND ku.datecreated = lc.datecreated
ORDER BY datecreated,userid
;
--Jeff Moden
May 20, 2020 at 6:23 am
Thanks so much. I'm feeding this data into a line chart, so if each user doesn't have a record for each month, then they won't show up on the line chart. That's why I need them to at least have a 0 for each month. With that being said, the query you provided works great; however, I think I might not have been clear with what I'm needing. Your query generates a record for every day, I simply need a record for the first day of each month. So, userid 4 is a great example, because this user only has records for 12/1/2019, 1/1/2020, 2/1/2020, and 3/1/2020. I need that user to have these records:
(4, 1, 15, 1, 1, 1, 9, 0, 1, 7, '12/1/2019'),
(4, 1, 3, 2, 4, 3, 16, 0, 0, 5, '1/1/2020'),
(4, 1, 134, 0, 0, 0, 2, 0, 0, 89, '2/1/2020'),
(4, 2, 26, 2, 2, 0, 2, 0, 10, 0, '3/1/2020'),
(4, 0, 0, 0, 0, 0, 0, 0, 0, 0, '4/1/2020'), --I need the query to generate this row
(4, 0, 0, 0, 0, 0, 0, 0, 0, 0, '5/1/2020') --I need the query to generate this row
I hope this makes better sense. Thank you again for your help with this!
Jordon
May 20, 2020 at 7:00 am
Crud... I have a bug in the code I just posted here... it missed 12/1/2019 and so I've removed the code... I'll be back soon.
--Jeff Moden
May 20, 2020 at 7:10 am
OK... here we go... code looks almost the same as before but added a couple of tweaks to do it just for the first of the month... I also fix the same bug in the previous code above.
WITH cteDateRange AS
(
SELECT MinDate = DATEADD(mm,DATEDIFF(mm,0,MIN(CONVERT(DATETIME,datecreated,110))),0)
,MaxDate = DATEADD(mm,DATEDIFF(mm,0,MAX(CONVERT(DATETIME,datecreated,110))),0)
FROM dbo.legalchanges
)
,cteDateDomain AS
(
SELECT datecreated = DATEADD(mm,t.N,dr.MinDate)
FROM cteDateRange dr
CROSS APPLY dbo.fnTally(0,DATEDIFF(mm,dr.MinDate,dr.MaxDate)) t
)
,cteKeyUp AS
(
SELECT lc.userid
,dt.datecreated
FROM cteDateDomain dt
CROSS JOIN dbo.legalchanges lc
GROUP BY dt.datecreated,lc.userid
)
SELECT ku.userid
,TCChanges = ISNULL(lc.TCChanges ,0)
,NonCompChanges = ISNULL(lc.NonCompChanges ,0)
,ActChanges = ISNULL(lc.ActChanges ,0)
,CatChanges = ISNULL(lc.CatChanges ,0)
,SubCatChanges = ISNULL(lc.SubCatChanges ,0)
,ExFromCompChanges = ISNULL(lc.ExFromCompChanges,0)
,MatterChanges = ISNULL(lc.MatterChanges ,0)
,BlockChanges = ISNULL(lc.BlockChanges ,0)
,VagueChanges = ISNULL(lc.VagueChanges ,0)
,ku.datecreated
FROM cteKeyUp ku
LEFT JOIN dbo.legalchanges lc
ON ku.userid = lc.userid
AND ku.datecreated = lc.datecreated
ORDER BY datecreated,userid
;
--Jeff Moden
May 20, 2020 at 1:33 pm
That worked perfectly! Thank you so much!!!
Jordon
May 20, 2020 at 2:29 pm
You're welcome and thank you for the feedback. The question now is... do you know how it works? I ask because you're the one that is going to have to support it.
--Jeff Moden
May 20, 2020 at 5:59 pm
Play with the idea of having a calendar table and then doing a set-oriented EXCEPT operator to find the missing dates and insert a row with the dummy values you need. I have no idea how effective it will be in SQL Server; in other SQL products, it works very very well 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
May 20, 2020 at 9:09 pm
Play with the idea of having a calendar table and then doing a set-oriented EXCEPT operator to find the missing dates and insert a row with the dummy values you need. I have no idea how effective it will be in SQL Server; in other SQL products, it works very very well 🙂
Got code? 😉
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply