dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] Pivot or Crosstab Query?

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,697

    Resolved [RESOLVED] Pivot or Crosstab Query?

    Cant wrap my head around this one being Friday maybe lol.

    Need the counts of a single field but based upon multiple date range count criteria.


    Code:
    Table1
    Field1
    ----------------
    11/14/2013
    11/8/2013
    10/25/2013
    10/30/2013
    10/30/2013
    So I want to display the counts in a result set with each date range count as a separate aliased field.

    Code:
    Result:
    
    Today | Last Week | 30 Days Ago
    -----------------------------------------------
    0     |     1     |    5
    Table1 is a temp table already filtered to contain only records that are 30 days old or newer.

    Thanks
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,697

    Re: Pivot or Crosstab Query?

    This is what I had come up with but invalid numbers coming back

    Code:
    select count([7_Days]) as '7_Days', count([30_Days]) as '30_Days'
    from 
       (
    	select 
    		count(datediff(day, upload_package_received_date, getdate())) as '7_Days',
    		0 as '30_Days'
    	from #temp_uploadpackage
    	group by upload_package_received_date
    	having datediff(day, upload_package_received_date, getdate()) <= 7
    	) as t1 group by [7_Days], [30_Days]
    	
    union
    select count(t2.[7_Days]) as '7_Days', count(t2.[30_Days]) as '30_Days'
    from 
       (
    	select 
    		0 as '7_Days',
    		count(datediff(day, upload_package_received_date, getdate())) as '30_Days'
    	from #temp_uploadpackage
    	group by upload_package_received_date
    	having datediff(day, upload_package_received_date, getdate()) <= 30
    	) as t2 group by [7_Days], [30_Days]
    Code:
    5   |   5
    Should be

    Code:
    0   |   5
    Ps, I left out the "Today" query as its just to get it working correctly first.
    Last edited by RobDog888; Nov 15th, 2013 at 08:43 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,423

    Re: Pivot or Crosstab Query?

    Code:
    	 SELECT SUM(CASE WHEN DATEDIFF(DAY, upload_package_received_date, GETDATE()) = 0   THEN 1 ELSE 0 END) AS Today, 
    		SUM(CASE WHEN DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 7  THEN 1 ELSE 0 END) AS Last_Week, 
    		SUM(CASE WHEN DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 30 THEN 1 ELSE 0 END) AS [30 Days Ago]
              FROM temp_uploadpackage
    Alternative:
    Code:
    SELECT (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) = 0)   AS Today, 
           (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 7)  AS Last_Week, 
           (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 30) AS [30 Days Ago]
    At first sight, the first query looks better (single query vs 3 subqueries in the second example) but it's not true since both together in execution plan show that cost for the first is 100% compared to 0% for the second.
    Attached Images Attached Images  
    Last edited by jcis; Nov 16th, 2013 at 12:51 AM.

  4. #4

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,697

    Re: Pivot or Crosstab Query?

    Got back on to this Friday and got it based upon my original query with low execution resources.


    Code:
    SELECT SUM([1_DAY]) AS [1_DAY], SUM([7_Days]) AS [7_DAYS], SUM([30_Days]) AS [30_DAYS]
    FROM 
       (
    	SELECT 
    		COUNT(DATEDIFF(DAY, upload_package_received_date, GETDATE())) AS [1_DAY],
    		0 AS [7_Days],
    		0 AS [30_Days]
    	FROM #temp_uploadpackage
    	GROUP BY upload_package_received_date
    	HAVING DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 0
    	) AS T1 GROUP BY [1_DAY], [7_Days], [30_Days]
    	
    UNION
    SELECT SUM([1_DAY]) AS [TODAY], SUM([7_Days]) AS [7_Days], SUM([30_Days]) AS [30_Days]
    FROM 
       (
    	SELECT 
    		0 AS [1_DAY],
    		COUNT(DATEDIFF(DAY, upload_package_received_date, GETDATE())) AS [7_Days],
    		0 AS [30_Days]
    	FROM #temp_uploadpackage
    	GROUP BY upload_package_received_date
    	HAVING DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 7
    	) AS t2 GROUP BY [1_DAY], [7_Days], [30_Days]
    	
    UNION
    SELECT SUM([1_DAY]) AS [TODAY], SUM([7_Days]) AS [7_Days], SUM([30_Days]) AS [30_Days]
    FROM 
       (
    	SELECT 
    		0 AS [1_DAY],
    		0 AS [7_Days],
    		COUNT(DATEDIFF(DAY, upload_package_received_date, GETDATE())) AS [30_Days]
    	FROM #temp_uploadpackage
    	GROUP BY upload_package_received_date
    	HAVING DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 30
    	) AS T3 GROUP BY [1_DAY], [7_Days], [30_Days]
    Attached Images Attached Images  
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    1,948

    Re: [RESOLVED] Pivot or Crosstab Query?

    Thanks for presenting the Execution plan with this query.

    Code:
    SELECT (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) = 0)   AS Today, 
               (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 7)  AS Last_Week, 
               (SELECT COUNT(1) FROM temp_uploadpackage WHERE DATEDIFF(DAY, upload_package_received_date, GETDATE()) <= 30) AS [30 Days Ago]
    I believe we have queries using single based integrated with Sum/Case statements are per your example. Time to check the SP's.

    Fingers crossed!

    Greg
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal |Programming | GitHub
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  6. #6

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,697

    Re: [RESOLVED] Pivot or Crosstab Query?

    No prob Greg. Im not a db guru but hopefully they are not intensive
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width