Click here to Skip to main content
14,770,630 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi all!
I have a database with this table:
#Report: link image table
http://prntscr.com/8h43p4[^]

How to query output data the following results:
http://prntscr.com/8h44r5[^]

Thanks every one!
Posted
Comments
RedDk 16-Sep-15 17:18pm
   
Not going to follow those links. For help, post code and error messages that occur at runtime.
deepankarbhatnagar 17-Sep-15 3:10am
   
Not getting, please explain

Try this

DECLARE @TBL TABLE
(
	ID INT IDENTITY,
	REPORTDATE DATETIME,
	GAMENAME VARCHAR(50),
	TOTAL  INT
)

INSERT INTO @TBL (REPORTDATE, GAMENAME, TOTAL)
SELECT '2015-09-05', 'Ninja Runner', 2000 UNION ALL
SELECT '2015-09-05', 'ZigZag', 1500 UNION ALL
SELECT '2015-09-05', 'Sky Force', 8000 UNION ALL
SELECT '2015-09-06', 'ZigZag', 300 UNION ALL
SELECT '2015-09-06', 'Sky Force', 2500 UNION ALL
SELECT '2015-09-07', 'Ninja Runner', 700 UNION ALL
SELECT '2015-09-07', 'Sky Force', 1000 UNION ALL
SELECT '2015-09-08', 'ZigZag', 10000


SELECT TMP.REPORTDATE, TMP.GAMENAME, ISNULL(T.TOTAL, 0)
FROM
(
	SELECT distinct GAMENAME, A.REPORTDATE
	FROM	@TBL
	CROSS JOIN
	(
	SELECT '2015-09-03' as 'REPORTDATE' UNION ALL
	SELECT '2015-09-04' UNION ALL
	SELECT '2015-09-05' UNION ALL
	SELECT '2015-09-06' UNION ALL
	SELECT '2015-09-07' UNION ALL
	SELECT '2015-09-08'
	) A 
) TMP LEFT OUTER JOIN @TBL T ON TMP.GAMENAME = T.GAMENAME AND TMP.REPORTDATE = T.REPORTDATE
order by 1, 2
   
Comments
7045Jeegnesh 24-Sep-15 8:57am
   
really? Hardy...Did u Understand Question? and O @Mr.thang Explain that U need All Date Between Min To Max Date
_Asif_ 24-Sep-15 10:43am
   
Did you understand the answer? you probably need to look at the images again and try execute the script that i have provided :)
Mr.thang 31-Mar-16 0:40am
   
Thanks @_Asif_ !
Select column_name, column_name, column_name from #report
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900