SQL Server Select the data and generate the SELECT for each day from date to date?

xqkwcwgp 于 2023-06-04 发布在其他

关注(0)|答案(1)|浏览(172)

I have this SELECT statement in SQL Server:

select testname as 'Test', 
       tests_morning , 
       tests_evening ,
       Date
from  labtests_hajj 
left join departments_statistics on departments_statistics.test_id = labtests_hajj.testid 
inner join departments on labtests_hajj.dept_id = departments.dept_id

The output now like this:

Test      tests_morning         tests_evening Date 
CBC       null                  null          null 
CALCIUM   null                  null          null   
SODIUMN   null                  null          null

How can I get the output as script and add the date also depends on date range for example I need the output for 3 days to be like this :

Test      tests_morning      tests_evening   Date 
CBC       null                  null     01/06/2023 
CALCIUM   null                  null     01/06/2023  
SODIUMN   null                  null     01/06/2023

CBC       null                  null     02/06/2023 
CALCIUM   null                  null     02/06/2023  
SODIUMN   null                  null     02/06/2023     
                                           
CBC       null                  null     03/06/2023 
CALCIUM   null                  null     03/06/2023  
SODIUMN   null                  null     03/06/2023

How can I do the select and put 2 dates and show the output like thie?

sql-server

来源：https://stackoverflow.com/questions/76389299/select-the-data-and-generate-the-select-for-each-day-from-date-to-date

1条答案

按热度按时间

7nbnzgx91#

There are have two ways

use DimDate or calendar table
you create a date table yourself with CTE

With DimDate

declare @StartData date='2023-02-01'
declare @EndData date='2023-02-06'
;with _t as 
(
    select testname as 'Test', 
           tests_morning , 
           tests_evening ,
           Date
    from  labtests_hajj 
    left join departments_statistics 
                     on departments_statistics.test_id = labtests_hajj.testid 
    inner join departments on labtests_hajj.dept_id = departments.dept_id

)
select 
             a.Test
            ,a.tests_morning
            ,a.tests_evening
            ,s.Date_ as  Date
from _t 
cross apply (select * from DimDate where date_ between  @StartData and @EndData )s

If your date range is consecutive, you can create the desired data by setting the start and end dates with CTE.

declare @StartData date='2023-02-01'
declare @EndData date='2023-02-06'
;WITH List
as
(
    SELECT @StartData as Date_
    UNION ALL
    SELECT DATEADD(day, 1, Date_) as 'MonthStart'
    FROM List
    where Date_<= @EndData
),
_t as 
(
    select testname as 'Test', 
           tests_morning , 
           tests_evening ,
           Date
    from  labtests_hajj 
    left join departments_statistics on departments_statistics.test_id = labtests_hajj.testid 
    inner join departments on labtests_hajj.dept_id = departments.dept_id
)
select 
              a.Test
            ,a.tests_morning
            ,a.tests_evening
            ,s.Date_ as  Date
from _t  a
cross apply (select * from List)s

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Select the data and generate the SELECT for each day from date to date?

1条答案

相关问题

热门标签

最新问答