rdbms

How can I create report in SQL based on query parameter date and its corresponding weekday?

My SQL table is as underenter image description here

The data is being updated by users on a daily basis and report_date is changed as when data updated by any user. Now I need to generate a report from the above table based on the date as a parameter. For example, If the query parameter (Report_date) is 1-Feb-20 then report should be Rly, Sum of Trains on weekday corresponding to the query date (sum of trains where Sch_SAT is Y as Saturday is weekday for 1-Feb-20), Sum of trains (where Report_date is equal to query date and Sch_SAT is Y), Sum of Trains where Sch_SAT is Y and Report_date is not equal to query date. The desired Report is as under: enter image description here

How can I generate the above report?

Solution

  • I have achieved the desired results by splitting & rejoining the table. My SQL query (stored procedure) is

    USE [Loco_bank]
GO
 /****** Object:  StoredProcedure [dbo].[HOG_summary]    Script Date: 02/13/2020 10:19:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE  [dbo].[HOG_summary] @Sunday varchar(1), @Monday varchar(1), @Tuesday varchar(1), @Wednesday varchar(1), @Thursday varchar(1), @Friday varchar(1), @Saturday varchar(1), @dt DATETIME AS

Begin

SELECT  Zrly as Master_Zrly, COUNT(Train) as Trains_sch INTO #tab1 FROM Hotel_load WHERE ([Saturday] = @Saturday OR @Saturday IS NULL) AND ([Sunday] = @Sunday OR @Sunday IS NULL) AND ([Monday] = @Monday OR @Monday IS NULL) AND ([Tuesday] = @Tuesday OR @Tuesday IS NULL) AND ([Wednesday] = @Wednesday OR @Wednesday IS NULL) AND ([Thursday] = @Thursday OR @Thursday IS NULL) AND ([Friday] = @Friday OR @Friday IS NULL) 
GROUP BY Zrly 

  SELECT  Zrly, COUNT(updated_on) as Reported, COUNT(HOG_loco_type_master) as HOG_Run, (COUNT(updated_on) - COUNT(HOG_loco_type_master)) as NONHOG_Run  INTO #tab2 FROM Hotel_load WHERE ([Saturday] = @Saturday OR @Saturday IS NULL) AND ([Sunday] = @Sunday OR @Sunday IS NULL) AND ([Monday] = @Monday OR @Monday IS NULL) AND ([Tuesday] = @Tuesday OR @Tuesday IS NULL) AND ([Wednesday] = @Wednesday OR @Wednesday IS NULL) AND ([Thursday] = @Thursday OR @Thursday IS NULL) AND ([Friday] = @Friday OR @Friday IS NULL) AND updated_on = @dt GROUP BY Zrly 

SELECT #tab1.Master_Zrly, #tab1.Trains_sch, #tab2.Zrly, #tab2.Reported, #tab2.HOG_Run, #tab2.NONHOG_Run FROM #tab1
LEFT JOIN #tab2 ON #tab1.Master_Zrly = #tab2.Zrly

End

    My query string is:

     http://127.0.0.1/HOG/HOG_Report_BD.aspx?Wednesday=Y&dt=12-Feb-2020

    And Output is

    | Master_Zrly | Trains_sch | Reported | HOG_Run | NONHOG_Run |
|-------------|------------|----------|---------|------------|
| SCR         | 25         | 9        | 18      | 2          |
| NCR         | 9          | 2        | 5       | 4          |
| SWR         | 13         | 4        |         |            |
| ER          | 28         | 7        | 22      | 1          |
| WCR         | 13         | 6        | 7       | 5          |
| CR          | 40         | 18       | 21      | 10         |
| KR          | 1          | 1        |         |            |
| NWR         | 17         | 16       |         |            |
| ECoR        | 11         | 2        |         |            |
| NR          | 99         | 30       | 62      | 6          |