CODEWITHSUNDEEP
rdbms
user1185088
user1185088

Reputation: 99

How to 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

Is it possible to generate the above report? If possible, please help.

Upvotes: 0

Views: 57

Answers (1)

user1185088
user1185088

Reputation: 99

Thanks for valuable tips for asking questions in presentable form on this forum. I will keep in mind in future issues. 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          |

Upvotes: 1

Related Questions