Need query to add dummy records

Question

Post reply

Need query to add dummy records

Bhuvnesh

SSC Guru

Points: 59351
More actions
January 20, 2014 at 4:56 am

#284652

declare @data table (CorpId INT, Days CHAR(3), Value MONEY)
INSERT INTO @data
SELECT 1, 'Sun' , 789.00
UNION SELECT 1, 'TUe' , 66.00
UNION SELECT 1,'Thu' , 566.00
UNION SELECT 1, 'Fri' , 98.00
UNION SELECT 1, 'Sat' , 789.00
UNION SELECT 2, 'Mon' , 234.00
UNION SELECT 2, 'Wed' , 298.00
UNION SELECT 2,'Fri' , 22.00
UNION SELECT 2, 'Sat' , 77.00
SELECT * from @data
--Desired OUtput
/*
CorpIdDaysValue
1Fri98.00
1Sat789.00
1Sun789.00
1Thu566.00
1TUe66.00
1MOn0.00 --Needs to insert here
1Wed0.00 --Needs to insert here
2Fri22.00
2Mon234.00
2Sat77.00
2Wed298.00
2TUe0.00 --Needs to insert here
2Thu0.00 --Needs to insert here
2Sun0.00 --Needs to insert here
*/
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 2 posts - 1 through 2 (of 2 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

declare @data table (CorpId INT, Days CHAR(3), Value MONEY)

INSERT INTO @data

SELECT 1, 'Sun' , 789.00

UNION SELECT 1, 'TUe' , 66.00

UNION SELECT 1,'Thu' , 566.00

UNION SELECT 1, 'Fri' , 98.00

UNION SELECT 1, 'Sat' , 789.00

UNION SELECT 2, 'Mon' , 234.00

UNION SELECT 2, 'Wed' , 298.00

UNION SELECT 2,'Fri' , 22.00

UNION SELECT 2, 'Sat' , 77.00

-- set up a matrix which contains all values of [CorpId] and [days],

-- then left join your table to it.

-- the new column [daynum] allows you to sort the days whatever way you like.

SELECT

c.CorpId,

x.[dayname],

Value = ISNULL(d.Value,0)

FROM (SELECT DISTINCT CorpId FROM @data) c

CROSS JOIN (VALUES (1, 'Mon'), (2, 'Tue'),(3, 'Wed'),(4, 'Thu'),

(5, 'Fri'),(6, 'Sat'),(7, 'Sun')) x (daynum,[dayname])

LEFT JOIN @data d ON d.CorpId = c.CorpId

AND d.Days = x.[dayname]

ORDER BY c.CorpId, x.daynum

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden